So how do we manage permissions in a Database Project? Well the logins and users are pretty straight forward, however sadly the grants are not as intuitive as we’d like (unless you find manually editing xml documents intuitive), but the next version is supposed to be gui driven. However right now I hope you enjoy editing xml.
So first we take the Server project and create the login for the user (if don’t know the difference between Logins and Users go read K. Brian Kelley’s post here, I’ll wait). If you are in Schema view (and if not, why not?) make sure to go under Server Level Objects –> Security –> Logins, not the Security –> Users path, as you want a Server Login, not a master db User
.
You’ll also notice in the Add new item dialog that there is a different template for each:
- Login (Windows Auth)
- Login (Sql Server)
At this point you might also be tempted in creation of the login to assign a default database, avoid that temptation. When you assign a default database, that user database must already exist, however when you deploy the server project (i.e., the master database) there’s a good chance the user database won’t exist yet, I promise we’ll take care of it later though.
CREATE LOGIN TestLogin WITH PASSWORD = 'P@ssw0rd1'
So now that you have a server login in the server project we need to create a database user (remember to reference the server project in the database project). This time you can go under the Security –> Users path.
CREATE USER [TestUser]
FOR LOGIN [TestLogin]
WITH DEFAULT_SCHEMA = dbo;
Now we have a login and a user created, lastly we set the default database in the database project post-Deployment script:
ALTER LOGIN [TestLogin] WITH DEFAULT_DATABASE=[$(DatabaseName)]
As you see I’m using the variable of the database name since I can change the database name for each deployment:
Taking a look at pertinent parts of the deployment sql scripts we have (remember this is in SQLCMD mode):
/*
Deployment script for master -LocalServer.sql
*/
:setvar DatabaseName "master"
GO
USE [$(DatabaseName)]
GO
PRINT N'Creating TestLogin...';
GO
CREATE LOGIN [TestLogin]
WITH PASSWORD = N'P@ssw0rd1', DEFAULT_DATABASE = master;
GO
------------------------------
/*
Deployment script for AdventureWorks - AdventureWorks.sql
*/
:setvar DatabaseName "AdventureWorks"
GO
CREATE USER [TestUser] FOR LOGIN [TestLogin];
GO
ALTER LOGIN [TestLogin] WITH DEFAULT_DATABASE=[$(DatabaseName)]
GO
0 Comments.