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.
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 userbut 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. |
|
|
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? |
|
|
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. |
|
|
|
|
|
|
|