having trouble granting role to user

Question

having trouble granting role to user

vxhughes

SSCommitted

Points: 1627
More actions
June 18, 2013 at 2:17 pm

#275321

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.

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

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

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 SSCommitted Points: 1627 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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 SSCommitted Points: 1627 More actions · Answer 4

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 SSC-Dedicated Points: 33507 More actions · Answer 5

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 SSCommitted Points: 1627 More actions · Answer 6

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 SSCommitted Points: 1627 More actions · Answer 7

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 SSC-Dedicated Points: 33507 More actions · Answer 8

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 SSCommitted Points: 1627 More actions · Answer 9

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.