November 6, 2008 at 12:36 pm
HI
I want to create a sql script which will give me all the table/view/function permissions a userid on a particular database has.
Can anyone please help .
Note that i am using SQL SERVER 2000
November 13, 2008 at 5:22 am
SELECT name, xtype
FROM dbo.sysobjects
WHERE uid = USER_ID('dbo')
Substitute dbo for whatever DOMAIN\USER you want to query on.
Run this in query analyser and select the DB from the dropdown list.
alternatively, add the line:
USE (Databasename)
at the start of the query.
November 15, 2008 at 1:38 am
That's a good, simple answer Ian.
However if Koustav also wanted much more detail, there's a really excellent script at
http://www.sqlservercentral.com/Scripts/Maintenance+and+Management/31598
It will:
* allow you to put a whole list of users
* obtain the information for all databases in the instance
* output the T_SQL code necessary to recreate the permissions
* include the SQL logins and roles
Highly recommended 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply