Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 following the literature, but I can’t grant role

Author  Topic 

Charles Egan
Starting Member

21 Posts

Posted - 2013-06-18 : 16:21:02
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 18:10:58
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 - 2013-06-18 : 23:33:11
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-20 : 14:57:55
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
   

- Advertisement -