We have a requirement to run a script using generic account to confirm that all databases on various SQL Servers are up and running.
I can write the vbscript for this requirement. The issue is what min priv can I give to the generic account used for the script so that it is able to connect to every database in the Server ? In Db2 I could have given the account connect authority to each database.
You can create a user with no specific permissions in each database. This user would inherit whatever permissions are granted to the public role. Typically (by best practices, anyway), this will be the system catalog, and that's it. To ensure that new databases are set up correctly, make sure this user is added to the model database, as well.
Edit the following script to use the appropriate Windows Login, then log into SQL Server as a sysadmin and execute the script.
DECLARE @cWindowsLogin sysname
DECLARE @cCmd NVARCHAR(999)
SET @cWindowsLogin = 'YourDomain\YourLogin'
SET @cCmd = 'EXECUTE [?]..sp_grantdbaccess ''' + @cWindowsLogin + ''''
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = @cWindowsLogin)
EXECUTE master.dbo.sp_grantlogin @cWindowsLogin
EXECUTE master.dbo.sp_MSforeachdb @cCmd
This script will give the specified Windows Login the ability to log into the server, then grant it only the ability to connect to each of the databases on that server... Connect privleges, and nothing more. Because the script grants access to the model database which is the template for creating new databases, the windows login will automatically have access to every database created after the script is run.
Thanks a ton for the script.
As Mcrowley pointed out, the only 2 options are :
a) use a sysadmin account for the server
b) or Use a generic account for the server and have a corresponding user in each database with min priv like public role.
Your script helped a lot. I ended up creating a vbscript which takes a list of 40 servers as input. It goes ahead and creates a login if not there and then it creates a user in every non system database.
Instead of sp_grandbaccess, I used the "create User" syntax for 2 reasons. Sp_grantdbaccess wont work in future versions and it creates a schema with the same name as the user in SQL 2005. With "Create User" syntax, the default schema is dbo.