Script to list Users with SA Role on the server

I am wanting to get a list of all the users on a SERVER who has the
System Administrator role assigned to their login. I was wondering if
there is a script or table(s) that I could refer to instead of looking
at all the individual login names. We are using SQL Server 2000.
Any help on this regard will be greatly appreciated.
ThanksYou can use the Stored Procedure sp_helpsrvrolemember to view the members of
a fixed server role.
ie.
EXEC sp_helpsrvrolemember 'sysadmin'
- Peter Ward
WARDY IT Solutions
"shub" wrote:

> I am wanting to get a list of all the users on a SERVER who has the
> System Administrator role assigned to their login. I was wondering if
> there is a script or table(s) that I could refer to instead of looking
> at all the individual login names. We are using SQL Server 2000.
> Any help on this regard will be greatly appreciated.
> Thanks
>

Script to list Users with SA Role on the server

I am wanting to get a list of all the users on a SERVER who has the
System Administrator role assigned to their login. I was wondering if
there is a script or table(s) that I could refer to instead of looking
at all the individual login names. We are using SQL Server 2000.
Any help on this regard will be greatly appreciated.
Thanks
You can use the Stored Procedure sp_helpsrvrolemember to view the members of
a fixed server role.
ie.
EXEC sp_helpsrvrolemember 'sysadmin'
- Peter Ward
WARDY IT Solutions
"shub" wrote:

> I am wanting to get a list of all the users on a SERVER who has the
> System Administrator role assigned to their login. I was wondering if
> there is a script or table(s) that I could refer to instead of looking
> at all the individual login names. We are using SQL Server 2000.
> Any help on this regard will be greatly appreciated.
> Thanks
>

Script to list Users with SA Role on the server

I am wanting to get a list of all the users on a SERVER who has the
System Administrator role assigned to their login. I was wondering if
there is a script or table(s) that I could refer to instead of looking
at all the individual login names. We are using SQL Server 2000.
Any help on this regard will be greatly appreciated.
ThanksYou can use the Stored Procedure sp_helpsrvrolemember to view the members of
a fixed server role.
ie.
EXEC sp_helpsrvrolemember 'sysadmin'
- Peter Ward
WARDY IT Solutions
"shub" wrote:
> I am wanting to get a list of all the users on a SERVER who has the
> System Administrator role assigned to their login. I was wondering if
> there is a script or table(s) that I could refer to instead of looking
> at all the individual login names. We are using SQL Server 2000.
> Any help on this regard will be greatly appreciated.
> Thanks
>

Script to copy permissions for all objects given to a user or a role

How would I, using a sql script, copy permissions assigned to a user
or a role in one or more databases to another user or a role in their
respective databases?

Help appreciatedgudia97@.yahoo.com (gudia) wrote in message news:<2130f7ff.0407071438.275b76f3@.posting.google.com>...
> How would I, using a sql script, copy permissions assigned to a user
> or a role in one or more databases to another user or a role in their
> respective databases?
> Help appreciated

First of all, I would avoid granting permissions to users, since this
becomes difficult to manage - if you always use roles, then things are
much easier. Even if you have only one user in a role today, at least
you won't have any extra work when you need to add a second one. Also,
you should keep a permissions script for each role, so you know which
permissions are correct, and you can run it for multiple roles.

To solve your issue right now, you could add one role to another, if
that's appropriate. If not, then you can reverse-engineer a
permissions script using a query like this:

select
case protecttype
when 205 then 'grant '
when 206 then 'revoke '
end +
case action
when 26 then ' references '
when 193 then ' select '
when 195 then ' insert '
when 196 then ' delete '
when 197 then ' update '
when 224 then ' execute '
end +
' on ' + object_name(id) + ' to TargetRole '
+ case when protecttype = 204 then ' with grant option' else '' end
from
sysprotects
where
uid = user_id('SourceRole')

See sysprotects in Books Online for more information. Note that this
query doesn't handle SELECT or UPDATE permissions on explicit columns,
but only where the permissions are on the whole table. It also does
not handle statement permissions (CREATE TABLE etc.), but you can
easily write a similar query for that.

Simon|||Simon:

Thanks. This will help as a starting point. I do use roles as oppossed
to users for permissioning.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

script db object permissions

I'm looking into processes to move to using database specific roles and migrating specifically assigned user permissions to these roles. I have made progress on listing permissions in the public role and assigning these same permissions to the new role that I have created for the given database. However, when I inspect permissions on tables and other objects, permissions have also been specifically granted for application users in the db. My ojective is to script out the permissions against the database objects (mainly user tables) and then use the output to assign the same permissions to the appropriate role instead. However, I'm having difficulty finding which system table stores the table and column level permissions.

If I do select * from sysobjects, I can see the user tables. However, I can't find the related tables that store user object permissions. For example, sysprotects doesn't store this info and, if I do an inner join on 'id' between these two tables, I don't see any user tables. Where do I look? Are there any useful 'already done' scripts/procs around for these purposes?

Regards,

CliveTry this one..
sp_helprotect
sskris|||well
i dont have an sql server with me now, but you could create a query between the sysobjects, sysusers, and sysprotects table and you could interpret the action and the protecttype columns
[Books Online] sysusers
[Books Online] sysobjects
[Books Online] sysprotects

if you want a quick way to view system tables in a graphical format you should download systbl.chm (http://download.microsoft.com/download/SQLSVR2000/sysmap/2000/WIN98MeXP/EN-US/systbl.chm) which is also available in your sql server 2000 resource kit.|||/*
This script creates a view to display users and objects that they have permissions for
and the permissions that are set
*/

use master
go
Create View VUserRights
as
SELECT top 100 percent
U.[Name] as UName
,O.Name as OName
,case xtype
when 'S' then 'System Table'
when 'P' then 'Stored Procedure'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'L' then 'Log'
when 'FN' then 'Scalar Function'
when 'IF' then 'Inlined Table-Function'
when 'PK' then 'PRIMARY KEY'
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'
when 'V' then 'View'
when 'X' then 'Extended Stored Procedure'
else cast(xtype as varchar(30))
end as XType

,Case p.[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'
Else cast([Action] as varchar(20))
End as 'Action'

,Case p.protecttype
When 204 Then 'GRANT_W_GRANT'
When 205 Then 'GRANT'
When 206 Then 'REVOKE'
Else cast(protecttype as varchar(20))
end as ProtectType

FROM sysusers U join sysprotects P
on u.uid = P.uid
Join sysobjects O
on P.id = O.id
where xtype <>'s'
order by U.uid ASC, O.xtype Desc

/*
Here are some calling statements
--2 is an oracle trick that i learned to
create a permissions assignment statement from exisiting metadata
*/

GO
--1
select * from vuserrights
Go
--2
select Protecttype + ' ' +
Action + ' ON ' +
Oname
-- +'('+ Xtype+')'
+ ' TO ' + Uname from vuserRights|||Thank you for the info. Nice View by the way.

I ended up writing a script that used sp_helprotect. It dumps out the permissions for a given user to a temp table and then I cursor through the temp table to 'grant' the permissions to a new role. eg. give me the Public permissions that have been granted against objects in this db and copy them to a new db role. Also, I can revoke the permsissions on Public as an option. So, I simply do:-

set @.GrantSQL ='Grant ' + @.action + ' on [' + @.Obj + '] to ' + @.Role
exec(@.GrantSQL)

However, although the above works fine on the db's I'm working on, I suspect that it wouldn't work with regard to database that have column level permssions. I tried setting a column level permission to see how my script would handle it. sp_helprotect reports the column level permssion but I haven't got around to modifiying my script to deal with it properly - currently it migrates the column level permssion in the source user/role to the target role as a table level permisson. In the loop that cursors through the temp table containing the sp_helprotect output, I presumably need to detect a column level permssion and branch to execute a grant statement that will apply a column level permission? Haven't had time to research this yet.

Thanks again,

Clive|||If you are comfortable with VB or VC, then SQL-DMO (http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_con01_2yi7.asp) offers exactly what you want.

-PatP|||Pat,

I am comfortable with VB but haven't doen anything with dmo yet. Got any examples?

Cheers,

Clive|||Just follow the link. There are zillions of examples scattered on the appropriate pages.

-PatP|||Zillions ?
it's more like a Quintillion.|||Zillions ?
it's more like a Quintillion.Ok, ok, ok... So what's a few zeros here and there between friends, eh? ;)

-PatP