Adding AD group to a db role

Question

Post reply

Adding AD group to a db role

sunny.tjk

SSCertifiable

Points: 5041
More actions
June 19, 2012 at 9:42 am

#258196

I've created a new AD group and assigned members to it. How do I add this AD group to a db_datareader role of a database? I'm little concerned since this a new AD group.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323522 More actions · Answer 1

the syntax is like this, is this what you were after?

EXEC sp_addrolemember N'db_datareader', N'MyDomain\NewADGroup'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 2

Lowell (6/19/2012)
the syntax is like this, is this what you were after?
EXEC sp_addrolemember N'db_datareader', N'MyDomain\NewADGroup'

I'm getting this error:

Incorrect syntax near 'T001371\ViewSSN'

Lowell SSC Guru Points: 323522 More actions · Answer 3

well it's a syntax issue, i'd guess you are missing something compared to my working example. the leading N or a single quote, or something.

Can you post the EXACT command you ran?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 4

Lowell (6/25/2012)
well it's a syntax issue, i'd guess you are missing something compared to my working example. the leading N or a single quote, or something.
Can you post the EXACT command you ran?

Here's what I ran:

EXEC sp_addrolemember N'SSN_GRANTED', N '[T001371\ViewSSN]'

go

and I also tried

EXEC sp_addrolemember N'SSN_GRANTED', N 'T001371\ViewSSN'

go

Lowell SSC Guru Points: 323522 More actions · Answer 5

two minor syntax issues;

the first is missing the single quote after "SSN_GRANTED"

EXEC sp_addrolemember N'SSN_GRANTED', N '[T001371\ViewSSN]'

and for both commands, there cannot be a space between the N and the single quote to tell the susten that it is NTEXT:

EXEC sp_addrolemember N'SSN_GRANTED', N'T001371\ViewSSN'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 6

Lowell (6/25/2012)
two minor syntax issues;
the first is missing the single quote after "SSN_GRANTED"
EXEC sp_addrolemember N'SSN_GRANTED', N '[T001371\ViewSSN]'
and for both commands, there cannot be a space between the N and the single quote to tell the susten that it is NTEXT:
EXEC sp_addrolemember N'SSN_GRANTED', N'T001371\ViewSSN'

Aah..My bad..

It again gave me an error:

User or role 'T001371\ViewSSN' does not exist in this database.

durai nagarajan SSCoach Points: 15778 More actions · Answer 7

Hello,

Frist create the login for domain group like this

CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

Regards
Durai Nagarajan

Nadrek SSC-Insane Points: 20039 More actions · Answer 8

Be aware that up through 2008 R2 at least, Windows Group logins are not able to set a default schema.

https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=238246&siteid=68

Adding AD group to a db role

Cookies on SQLServerCentral