Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Test SQL Server Login Permissions With SETUSER
From Wiki
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
- --Create the user
- EXEC SP_ADDLOGIN 'Albert', 'food', 'pubs'
- EXEC SP_ADDUSER 'Albert'
- CREATE TABLE dbo.test(id INT IDENTITY,datefield DATETIME)
- INSERT INTO test
- SELECT GETDATE()
- SELECT * FROM test -- as dbo
- -- let's run the select as Albert
- SETUSER 'Albert'
- 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
- SETUSER
- EXEC SP_DROPUSER 'Albert'
- 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
- EXEC SP_DROPUSER 'Albert'
- EXEC SP_DROPLOGIN 'Albert'
Contributed by: --SQLDenis 17:40, 7 June 2008 (GMT)
Part of SQL Server Admin Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.