Roles and User Permissions Script for SQLSERVER 20

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 Administration

Roles and User Permissions Script for SQLSERVER 20

Author

Topic

Suryahai
Starting Member

1 Post

Posted - 2012-06-04 : 10:30:51

hi anyone please post query for all sql permissions and role permissions of all users in a database for SQLSERVER 2000.
I did lot of research in the internet but i didn't find any script to resolve my issue...!

i badly required it asap.
Please ........!

m.esteghamat
Starting Member

47 Posts

Posted - 2012-06-05 : 03:50:19

hi
you want to know security statusof your server OR want to set some security for every users?

m.esteghamat
Starting Member

47 Posts

Posted - 2012-06-05 : 04:47:36

hi again
for get current status :

use Database
Select User_Name(SP.Uid), ' On '+Sj.Name ,
(Case Xtype
When 'C' Then 'CHECK constraint'
When 'D' Then 'Default or DEFAULT constraint'
When 'F' Then 'FOREIGN KEY constraint'
When 'L' Then 'Log'
When 'FN' Then 'Scalar function'
When 'IF' Then 'Inlined table-function'
When 'P' Then 'Stored procedure'
When 'PK' Then 'PRIMARY KEY constraint (type is K)'
When 'RF' Then 'Replication filter stored procedure '
When 'S' Then 'System table'
When 'TF' Then 'Table function'
When 'TR' Then 'Trigger'
When 'U' Then 'User table'
When 'UQ' Then 'UNIQUE constraint (type is K)'
When 'V' Then 'View'
When 'X' Then 'Extended stored procedure' End) As ObjectType ,
(Case Action
When 26 Then 'REFERENCES'
When 178 Then 'CREATE FUNCTION'
When 193 Then 'SELECT'
When 195 Then 'INSERT'
When 196 Then 'DELETE'
When 197 Then 'UPDATE'
When 198 Then 'CREATE TABLE'
When 203 Then 'CREATE DATABASE'
When 207 Then 'CREATE VIEW'
When 222 Then 'CREATE PROCEDURE'
When 224 Then 'EXECUTE'
When 228 Then 'BACKUP DATABASE'
When 233 Then 'CREATE DEFAULT'
When 235 Then 'BACKUP LOG'
When 236 Then 'CREATE RULE' End) As ActionType ,
(Case ProtectType
When 204 Then 'GRANT_W_GRANT'
When 205 Then 'GRANT'
When 206 Then 'REVOKE' End) As ProtectType , Columns
from dbo.sysprotects SP Inner Join dbo.sysobjects SJ
On Sp.id=SJ.id
where sp.uid <>0

wendyss
Starting Member

4 Posts

Posted - 2012-06-11 : 22:17:08

----------------------------------------------------------------------------------------------------------------------------------------
[url=http://www.rchelicopterus.com/]Rc Helicopter Parts[/url]|[url=http://minirchelicopter-reviews.com/]Mini Rc Helicopter[/url]|[url=http://rc-helicopters-reviews.com/]Rc Helicopters Reviews[/url]|[url=http://www.rchobbyforsale.com/]Rc Hobby[/url]

liuli wendys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources