SQL Server Database Permissions Checklist

0 Likes

In order to ensure error free upgrades and StarTeam performance, the SQL Server database must have the correct ownership settings. The checklist below provides checks and methods to ensure database health.

The database should be owned by the user specified in the StarTeam configuration properties. This can be checked in the properties dialog for the DB.

If the database is owned by a different user, ownership can be changed using:

exec sp_changedbowner 'newusername'

All tables and stored procedures should be owned by DBO. If this is not the case this can be rectified using the following script:

DECLARE
@OldOwner sysname,
@NewOwner sysname

SET @OldOwner = 'starteam'
SET @NewOwner = 'dbo'

DECLARE CURS CURSOR FOR
SELECT
name
FROM
sysobjects
WHERE
type = 'p'
AND
uid = (SELECT uid FROM sysusers WHERE name = @OldOwner)
AND
NOT name LIKE 'dt%' FOR READ ONLY

DECLARE @ProcName sysname

OPEN CURS

FETCH CURS INTO @ProcName

WHILE @@FETCH_STATUS = 0
BEGIN
IF @@VERSION >= 'Microsoft SQL Server 2005'
BEGIN
EXEC('alter schema ' @NewOwner ' transfer ' @OldOwner '.' @ProcName)
exec('alter authorization on ' @NewOwner '.' @ProcName ' to schema owner')
END
ELSE
EXEC('sp_changeobjectowner ''' @OldOwner '.' @ProcName ''', ''' @NewOwner '''')

FETCH CURS INTO @ProcName
END

CLOSE CURS
DEALLOCATE CURS

The oldname value should be edited to specify the non DBO username owning the tables or procedures.

Views should also be owned by DBO.

The views can be dropped, provided the above conditions are met, the views will then be recreated with correct ownership upon the next server startup.

 

Comment List
Related
Recommended