April 18, 2019 at 4:12 pm
We are making preparations to do away with mixed mode security and going to windows authentication only. So, I'm using something like the script below to create the logins and apply permissions to each database (this is greatly simplified, but hopefully I have all the relevant parts for one of my databases). However, after running the script, when I use the object explorer to look at the user mapping for my_user, the database my_database is not selected as I am expecting. Also, when I look under the Security\Users node for the database, the user has a tiny red x as part of the icon. I don't want to manually map the users via SSMS, so how do I script this properly to add the user mapping?
USE [master]
CREATE LOGIN [mydomain\my_user] FROM WINDOWS
GO
USE [my_database]
GRANT EXECUTE TO [mydomain\my_user]
GO
GRANT EXECUTE TO [mydomain\my_user]
GO
ALTER ROLE db_datareader ADD MEMBER [mydomain\my_user]
GO
ALTER ROLE db_datawriter ADD MEMBER [mydomain\my_user]
GO
April 18, 2019 at 4:39 pm
If I'm understanding you correctly, the users do not have connect privileges to the database. Assuming you actually want them to be able to connect to the database, the following should do the trick:
GRANT CONNECT TO [mydomain\my_user] ;
GRANT CONNECT TO [mydomain\my_user] ;
April 18, 2019 at 5:38 pm
Thanks, I actually had GRANT CONNECT as well, but only in the [master] database. I didn't have it for each database.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply