Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Test SQL Server Login Permissions With SETUSER

From Wiki

Jump to: navigation, search

Sometimes you get a request to create a login and you want to test the permissions before letting the user know that he can use the login. you just don't feel like login in and trying to run some SQL statements for every user. With the SETUSER statement you can eliminate that. Here is some code that explains how to use it

  1. --Create the user
  2. EXEC SP_ADDLOGIN 'Albert', 'food', 'pubs'
  3. EXEC SP_ADDUSER 'Albert'
  4.  
  5. CREATE TABLE dbo.test(id INT IDENTITY,datefield DATETIME)
  6. INSERT INTO test
  7. SELECT GETDATE()
  8.  
  9.  
  10. SELECT * FROM test -- as dbo
  11.  
  12. -- let's run the select as Albert
  13. SETUSER 'Albert'
  14. SELECT * FROM test -- as albert


Now you should get this error Server: Msg 229, Level 14, State 5, Line 1 SELECT permission denied on object 'test', database 'pubs', owner 'dbo'.

execute just the SETUSER statement and you can drop Albert since the user will be reset to the original user

  1. SETUSER
  2. EXEC SP_DROPUSER 'Albert'
  3. EXEC SP_DROPLOGIN 'Albert'


Or close the query window and in another window execute the code below to drop Albert You have to do this in another window since as Albert you don't have permissions to do this

  1. EXEC SP_DROPUSER 'Albert'
  2. EXEC SP_DROPLOGIN 'Albert'



Contributed by: --SQLDenis 17:40, 7 June 2008 (GMT)


Part of SQL Server Admin Hacks

364 Rating: 0.0/5 (0 votes cast)