Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

having trouble granting role to user Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 2:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:24 AM
Points: 41, 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.
Post #1464871
Posted Wednesday, June 19, 2013 2:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #1465004
Posted Wednesday, June 19, 2013 3:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:24 AM
Points: 41, 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.
Post #1465414
Posted Wednesday, June 19, 2013 4:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 AM
Points: 41,548, Visits: 34,470
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 2008, MVP
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

Post #1465419
Posted Wednesday, June 19, 2013 4:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:24 AM
Points: 41, 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.
Post #1465428
Posted Wednesday, June 19, 2013 11:45 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:59 AM
Points: 2,078, Visits: 2,410
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 . 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’! **
Post #1465461
Posted Thursday, June 20, 2013 8:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:24 AM
Points: 41, 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. 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.
Post #1465736
Posted Thursday, June 20, 2013 8:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:24 AM
Points: 41, 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.
Post #1465737
Posted Thursday, June 20, 2013 8:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #1465740
Posted Thursday, June 20, 2013 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 20, 2013 8:24 AM
Points: 41, 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.
Post #1465755
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse