SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 following the literature, but I can’t grant role
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Charles Egan
Starting Member

21 Posts

Posted - 06/18/2013 :  16:21:02  Show Profile  Reply with Quote
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.

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 06/18/2013 :  18:10:58  Show Profile  Reply with Quote
When you created the login, did you also map that user to the database? If you have not, that would be the problem. You can right-click on that login, and under User Mappings, check the database name, select appropriate database roles including the testing role. You can also do all of that via T-SQL scripts; in the dialogs, click the script button at the top left of the right panel, and it will generate the script for you.
Go to Top of Page

Charles Egan
Starting Member

21 Posts

Posted - 06/18/2013 :  23:33:11  Show Profile  Reply with Quote
Hi, James K -

In SQL Server Management Studio, when I right-click on Object Explorer | < database engine > | Security | Logins | NULL\test_user, User Mappings doesn't appear in the resulting drop-down menu.

Also, I'm a newbie, so I don't know what "in the dialogs" means.

Comments?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 06/20/2013 :  14:57:55  Show Profile  Reply with Quote
That "NULL\test_user" doesn't sound right. Usually the logins are DOMAINNAME\username (for windows authenicated users), or a username (for SQL authenicated users).

When you right click on a login and from the menu that appears, select properties, a popup window should appear. The window has several tabs towards the left. One of the tabs is "User Mapping". Click on that, and you will see the databases on the server listed.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000