June 19, 2009 at 8:28 am
I have been asked a question that I am unsure of how to accurately answer. I know the answer for each part, but the WHY?? I"m confused.
Question asked:
I have a script I created that adds a new user group (off of our AD ) to a SQL Server, I then go to each individual database and run an addrole and not a create user.
Example of code:
USE MASTER
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DOMAIN\HRO_QA')
DROP LOGIN [DOMAIN\HRO_QA]
GO
CREATE LOGIN [DOMAIN\HRO_QA] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
GRANT VIEW ANY DEFINITION TO [DOMAIN\HRO_QA]
/*now grant rights to inidividual databases*/
USE Applog
GO
EXEC sp_addrolemember 'db_datareader', 'DOMAIN\HRO_QA'
GO
I check everything out and the users can access their database still when this new AD group (with them added at the acitve directory level are in there).
The question I was asked was, why I chose not to put a CREATE USER?
Everything has created fine with the account people can access exactly what they need to, so why I would/would not user CREATE USER?
Do I have to?
If not, can someone explain why?
Thank you.
JJ:hehe:
June 19, 2009 at 8:36 am
I think that it's because a group used as a login, as in this case, doesn't need the user to be added.
FYI, with 2005, you ought to be using ALTER ROLE instead (http://msdn.microsoft.com/en-us/library/ms189775.aspx)
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 19, 2009 at 9:34 am
I think he means, Why he didn't have to create a database user(DOMAIN\HRO_QA)for the active directory group server login in the database.
well the answer is the sp_addrolemember proc checks where the user you are adding to role exists as a user in the database and if not creates that user. You can verify this by running a profiler trace while you execute the sp_addrolemember proc.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply