Granting access to all objects in a database role.

This is my favorite script to grant access to database objects, specifically for development team. It would be tough to manage permission sometimes by creating individual accounts and granting access to database objects directly. This is much better way to grant permission hope this helps. Copy past the complete query and you have to edit few portions of the script as necessary.


DECLARE
        @LoginName VARCHAR(128),
        @DBUserRole VARCHAR(128),
        @DatabaseName VARCHAR(100),
        @SQLScript VARCHAR(6000)

SET @LoginName = 'Login\login'  -- Windows Login DOMAIN\Username
SET @DBUserRole = 'ROLE_for_developers' -- This role is for developer access to the objects

IF EXISTS (SELECT * FROM master..syslogins WHERE [name] = @LoginName)
BEGIN
        DECLARE DatabaseCursor CURSOR FOR
                SELECT [name] FROM master..sysdatabases
                WHERE  name in  ('database1','database2')  -- or you can just specify one database name = ''
ORDER BY [name]
        OPEN DatabaseCursor
        FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

        WHILE @@FETCH_STATUS = 0
                BEGIN

                        PRINT 'Updating Database:  ' + @DatabaseName + ''

                        SET @SQLScript = '
                                USE ' + @DatabaseName + '
                                IF NOT EXISTS (SELECT * FROM ' + @DatabaseName + '..sysusers WHERE [name] = ''' + @DBUserRole + ''')
                                        EXEC sp_addrolemember '''+ @DBUserRole + ''',''' + @LoginName + ''''
                                                     
                                                          
                        PRINT @SQLScript
                        --EXEC (@SQLScript)

                        -- Modify script below to change object types or type of permissions: U = User table, etc.
                        SET @SQLScript = '
                        USE ' + @DatabaseName + '
                        DECLARE
                                @TableName VARCHAR(100),
                                @SQLScript VARCHAR(4000),
                                                @type varchar(20)

                        DECLARE TableCursor CURSOR FOR
                                SELECT [name], type FROM ' + @DatabaseName + '..sysobjects WHERE type in (''U'',''V'',''P'',''FN'',''SN'',''TF'') ORDER BY [name]
                        OPEN TableCursor
                        FETCH NEXT FROM TableCursor INTO @TableName,@type
                        WHILE @@FETCH_STATUS = 0
                                BEGIN
                                                      set @SQLScript =  Case
                                                                                    when @type = ''U'' then ''GRANT SELECT,References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type =''V'' then ''GRANT SELECT,References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type = ''P'' then ''GRANT Execute, References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type = ''FN'' then ''GRANT Execute, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type = ''TF'' then ''GRANT Select, References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''                           
                                                                                    when @type = ''SN'' then ''GRANT Select, Execute, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                End
                                      PRINT @SQLScript
                                        --EXEC(@SQLScript)

                                        FETCH NEXT FROM TableCursor INTO @TableName,@type
                                END
                        CLOSE TableCursor
                        DEALLOCATE TableCursor'

                     --PRINT @SQLScript
                       EXEC (@SQLScript)

                        FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

                END

        CLOSE DatabaseCursor
        DEALLOCATE DatabaseCursor
END
ELSE PRINT 'Login ' + @LoginName + ' does not exist.'

-Pramod K

Installing Service Pack on Active/Pasive Cluster Windows 2008 SQL Server 2008

Hi all,

This is really interesting to learn how to apply service pack on Windows 2008 SQL Server 2008 Active/Pasive Clustered environment.

Here we are following the method of applying Service pack on Pasive node first and move the resources to the active node and once service pack is applied on Pasive node do the vice versa. Follow the below notes and link to watch the video. 

Please click the links to watch the videos.

SP on Active/Pasvie Cluster 1
SP on Active/Pasive cluster 2


I have the below notes from the video, it will be helpful.

Before you go further please take a note of the Version on the SQL Server it will change once you apply Service pack. So in-order to verify the installation make a note of the version.
node#1 active
node#2 passive(install on passive node first)

1-  In cluster manager - right click sql server network cluster name - properties - advanced - then uncheck the     passive node#2(this is because we dont want it to fail to this node during the install since it can corrupt binaries)
2- install SP on passive node#2 (make sure sql services stopped on this node, etc)
3- once installed  manual failover services from node 1 to node 2 and update SP on node 1
 steps: A- SQLserver network cluster name advanced and re-select node 2 to add it back in. 
        B- In Cluster Manager move services(current owner)over to node#2 (now that its been patched)   
        C- Now remove node#1 by right clicking  sql server network cluster name - properties - advanced then uncheck node#1
4- install SP 1 on node#1
5- After install complets on node#1 we need to set owners again. Right click sqlserver network cluster name properties  - advanced and re-select node#1 to add it back in.
6- Fail services back to primary node and start services.

Before moving the resources between the nodes open SSMS and verify that the service pack has been applied.

-- David M and Pramod K