SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


having trouble granting role to user


having trouble granting role to user

Author
Message
vxhughes
vxhughes
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 3
I've submitted these SQL commands with no problem:

CREATE ROLE testing; -- create Role "testing"
GRANT CREATE TABLE TO testing; -- grant CREATE TABLE privilege to ROLE "testing"

Now I want to grant role "testing" to user "test_user".

But when I try

GRANT testing TO test_user;

or

GRANT ROLE testing TO test_user;

I get

Incorrect syntax near 'testing'.

When I try

EXEC sp_addrolemember testing, test_user;

I get

User or role 'test_user' does not exist in this database.

even though I HAD already created a new login for test_user in

Object Explorer | < database engine > | Security | Logins (right-click)

I did a search on

SQL how to GRANT ROLE to user

but couldn't find an answer that worked for me. Someone even suggested

GRANT testing ON test_user TO AdventureWorksDW;

but again I got

Incorrect syntax near 'testing'.

Incidentally, just to be sure, I tried all this using an Administrator level Windows user account and an Administrator level SQL Server account.

Also incidentally, I’m running SQL Server 2008 with Advanced Services under Windows XP Pro.

Any suggestions? Thanks for any help anyone can give.
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8648 Visits: 3718
A LOGIN is used to gain access on the instance-level, but (default) not on the database-level. You need to create a USER within the database and connect this to the LOGIN. After you have created the USER you are able to add this user to the ROLE.

USE {your_database_name}
CREATE ROLE testing; -- create Role "testing"
GRANT CREATE TABLE TO testing; -- grant CREATE TABLE privilege to ROLE "testing"

CREATE USER [test_user] FOR LOGIN [test_user]

EXEC sp_addrolemember testing, test_user



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
vxhughes
vxhughes
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 3
Hi, HanShi -

When I received your post, I had just run the following commands:

CREATE ROLE testing;
GRANT CREATE TABLE TO testing;
CREATE USER test_user FOR LOGIN [NULL\test_user];
EXEC sp_addrolemember testing, test_user;

(Yes, NULL is the SQL Server domain name on my PC.)

This worked fine, BUT I had said

CREATE ROLE testing;

rather than

USE AdventureWorksDW
CREATE ROLE testing;

Is there any way to tell (preferably via SQL commands, rather than via SQL Server management Studio, but either would be fine), which database my ROLE "testing" got associated with?

Also, do you know if there is a way to create a login (as opposed to creating a USER for a LOGIN) via SQL commands, rather than via SQL Server Management Studio?

Thanks for your help already, and thanks for any further information you could provide.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225437 Visits: 46321
vxhughes (6/19/2013)
Is there any way to tell (preferably via SQL commands, rather than via SQL Server management Studio, but either would be fine), which database my ROLE "testing" got associated with?


Would have been whatever database your connection was using at the time you ran the query

Also, do you know if there is a way to create a login (as opposed to creating a USER for a LOGIN) via SQL commands, rather than via SQL Server Management Studio?


CREATE LOGIN ...

See Books Online for the details.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


vxhughes
vxhughes
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 3
Hi, GilaMonster -

>Would have been whatever database your connection was using at the time you ran the query

But suppose you didn't know that, for whatever reason?

>CREATE LOGIN ...

Thanks, I'll check it out.
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8648 Visits: 3718
vxhughes (6/19/2013)
But suppose you didn't know that, for whatever reason

If you don't know that, then you would be shooting in the dark Exclamation. When you alter security rights you need to know what you are doing and where you are doing that.
You can use "SELECT DB_NAME()" to get the name of the database you are currently working at.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
vxhughes
vxhughes
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 3
In a professional situation, OBVIOUSLY I wouldn't be altering the security rights of a database when I didn't even know what database I was in. Exclamation What we're talking about here is when I play with my AdventureWorks sample databases (where I can't do any real, permanent damage) in order to learn SQL, and in so doing, following the directions found in tutorials, altered the security rights of a sample database without knowing which one I was in. Give me some credit - I'm not a complete idiot.
vxhughes
vxhughes
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 3
You can use "SELECT DB_NAME()" to get the name of the database you are currently working at.


Thanks - that's what I needed to know.
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8648 Visits: 3718
vxhughes (6/20/2013)
Give me some credit - I'm not a complete idiot.
Please forgive me if you take it personally. I didn't mean to offend you, just making a warning sign about the implications of altering security.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
vxhughes
vxhughes
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 3
Hi, HanShi -

Please forgive my terse tone - I was somewhat taken aback. Yes, I appreciate the dangers of working on databases without knowing what I'm doing. Hopefully, I WILL know precisely what I'm doing before I start to work on "real" databases.

Again, thanks very much for your help; it's much appreciated.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search