1

I'm trying to create a user in SQL Server. I can create the user fine and then I grant that user some privileges. Here is the code:

CREATE LOGIN TestLogin WITH password='abc';

 -- Now add user to database
USE TestDB;
CREATE USER TestUser FOR LOGIN TestLogin;
GO

use [TestDB]
GO
GRANT INSERT TO [TestUser]
GO
use [TestDB]
GO
GRANT SELECT TO [TestUser]
GO
use [TestDB]
GO
GRANT UPDATE TO [TestUser]
GO
use [TestDB]
GO
DENY DELETE TO [TestUser]
GO

After this I want to use this user in my web.config file. 

<add name="DBConnectionString"
     connectionString="Server=tcp:abc.database.windows.net;Database=TestDB;User ID=TestLogin;Password=abc;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True;" 
     providerName="System.Data.SqlClient" />

My question is: in the web.config file I have to use the LOGIN user. When I apply migrations, I don't want to run any DELETE statements so I created a USER and denied him the access. Can I use this user in the web.config file?

My goal is that when migrations are applied I don't want to execute DELETE. Will my approach work or what should I change?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
chillax786
  • 369
  • 4
  • 14
  • if your migration scripts include delete statements,they will fail.other than that i dont see any issue.You may also encrypt password in webconfig file if you think others might get azure password – TheGameiswar Jun 07 '16 at 19:56
  • What if I have multiple users for my db "TestDB" and each user has different privileges? – chillax786 Jun 07 '16 at 20:11
  • I don't follow your concern about having multiple users- your connection string is defined to use a specific user within web.config; other users having different privileges are irrelevant to `TestLogin`'s privileges. – Dan Rediske Jun 07 '16 at 20:57
  • Yeah, but I'm granting privileges to `TestUser` and not `TestLogin`. Sorry if it's a bit confusing. – chillax786 Jun 07 '16 at 21:12
  • My apologies- I missed that salient detail; You created a server level principal (the Login), and then created a database level principal (the user); if you aren't familiar with the difference, [here was a question on that](http://stackoverflow.com/questions/1134319/difference-between-a-user-and-a-login-in-sql-server). In your example, you mapped `TestLogin` -> `TestUser` on `TestDB` `TestLogin` will have the permission level you assigned to `TestUser` since you're connecting to `TestDB`. – Dan Rediske Jun 07 '16 at 21:33
  • Alright thanks, so when I apply a migration that includes DELETE statements, the migration shouldn't succeed right? – chillax786 Jun 07 '16 at 21:51
  • it depends ,if all are wrapped in a transaction,then it will fail – TheGameiswar Jun 08 '16 at 04:42
  • I believe it's best practice to avoid chaining questions in comments. I'm moving my explanation to an answer as well, since I believe it covers your first question fully. – Dan Rediske Jun 08 '16 at 23:51
  • You seem to be concerned with something that isn't limited to permissions, but rather with the operations within what you're calling "migrations". What do you mean by "migrations"? – Dan Rediske Jun 09 '16 at 21:03
  • @drediske. I was wanted to know what would happen if DB migrations were applied with restrictions on the DB. But I tested it, and it seems that the migration will fail if it tries to execute something that there is a restriction on. – chillax786 Jun 09 '16 at 23:15

1 Answers1

1

You created a server level principal (the Login), and then created a database level principal (the user) and modified it's permissions; if you aren't familiar with the difference, here was a question on that.

In your example, you mapped TestLogin -> TestUser on TestDB; this means that TestLogin will have the permission level you assigned to TestUser since you're connecting to TestDB. (So YES you will have denied DELETE to the connection defined in the connection string.)

As for your question in the comments about your migration- when executed via this connection string, as far as my understanding goes, it should fail.

Community
  • 1
  • 1
Dan Rediske
  • 852
  • 4
  • 14