Get all privileges from users
-
Hello,
Can anyone tell me what procedures can i use to get all the information of privileges of all users in an existing database(Instance)?
I'm getting a lot of SQL Server hosts,that the administration were made by..... no one, and now i need to check what privileges have users that were created, and what kind of problems should i get if i change their privilieges.
Sorry for this simple question.
Thanks,
Regards,
Jorge Mendes
Thanks a lot.
Regards,
Jorge Manuel Mendes -
Jorge
If you're using SQL Server 2000, use the code below. If you're on SQL Server 2005, use the sys.database_permissions view instead - you don't need a temp table. Full details are in Books Online.
John
CREATE
TABLE #Permissions (
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType nvarchar(10),
[Action] nvarchar(20),
[Column] sysname )
INSERT
INTO #Permissions EXEC sp_helprotect
SELECT Grantee,
Owner + '.' + OBJECT AS ObjectName,
ProtectType,
[Action],
[Column]
FROM #Permissions
ORDER BY Grantee
DROP
TABLE #Permissions
-
Hello John,
I like to thanks your amability but i dont know if you understand my point.
The question were that i need to get one report with permissions owned by all users in my instance.
Down on this page i put the output of your code, but i dont know what should i do with this output.
I apreciate your answer.
Thanks and regards,
Jorge Mendes
public dbo.dt_addtosourcecontrol Grant Execute .
public dbo.dt_addtosourcecontrol_u Grant Execute .
public dbo.dt_adduserobject Grant Execute .
public dbo.dt_adduserobject_vcs Grant Execute .
public dbo.dt_checkinobject Grant Execute .
public dbo.dt_checkinobject_u Grant Execute .
public dbo.dt_checkoutobject Grant Execute .
public dbo.dt_checkoutobject_u Grant Execute .
public dbo.dt_displayoaerror Grant Execute .
public dbo.dt_displayoaerror_u Grant Execute .
public dbo.dt_droppropertiesbyid Grant Execute .
public dbo.dt_dropuserobjectbyid Grant Execute .
public dbo.dt_generateansiname Grant Execute .
public dbo.dt_getobjwithprop Grant Execute .
public dbo.dt_getobjwithprop_u Grant Execute .
public dbo.dt_getpropertiesbyid Grant Execute .
public dbo.dt_getpropertiesbyid_u Grant Execute .
public dbo.dt_getpropertiesbyid_vcs Grant Execute .
public dbo.dt_getpropertiesbyid_vcs_u Grant Execute .
public dbo.dt_isundersourcecontrol Grant Execute .
public dbo.dt_isundersourcecontrol_u Grant Execute .
public dbo.dt_removefromsourcecontrol Grant Execute .
public dbo.dt_setpropertybyid Grant Execute .
public dbo.dt_setpropertybyid_u Grant Execute .
public dbo.dt_validateloginparams Grant Execute .
public dbo.dt_validateloginparams_u Grant Execute .
public dbo.dt_vcsenabled Grant Execute .
public dbo.dt_verstamp006 Grant Execute .
public dbo.dt_verstamp007 Grant Execute .
public dbo.dt_whocheckedout Grant Execute .
public dbo.dt_whocheckedout_u Grant Execute .
public dbo.dtproperties Grant Delete .
public dbo.dtproperties Grant Insert .
public dbo.dtproperties Grant References (All+New)
public dbo.dtproperties Grant Select (All+New)
public dbo.dtproperties Grant Update (All+New)
public dbo.syscolumns Grant Select (All+New)
public dbo.syscomments Grant Select (All+New)
public dbo.sysconstraints Grant Select (All)
public dbo.sysdepends Grant Select (All+New)
public dbo.sysfilegroups Grant Select (All+New)
public dbo.sysfiles Grant Select (All+New)
public dbo.sysforeignkeys Grant Select (All+New)
public dbo.sysfulltextcatalogs Grant Select (All+New)
public dbo.sysindexes Grant Select (All+New)
public dbo.sysindexkeys Grant Select (All+New)
public dbo.sysmembers Grant Select (All+New)
public dbo.sysobjects Grant Select (All+New)
public dbo.syspermissions Grant Select (All+New)
public dbo.sysprotects Grant Select (All+New)
public dbo.sysreferences Grant Select (All+New)
public dbo.syssegments Grant Select (All)
public dbo.systypes Grant Select (All+New)
public dbo.sysusers Grant Select (All+New)
Thanks a lot.
Regards,
Jorge Manuel Mendes -
Jorge
I don't understand exactly what you require, then. Please will you give an example of how your report should look.
Thanks
John
-
Hello John,
I've tried to get one output like this one.
/**********************/
| Login Information |
/**********************/
Login Information on HOSTNAME
Login Name Default Database
------------------------------ --------------------
sa master
BUILTIN\Administrators master
Infraxyz {database name}
infra1q2w {database name}
/***** User that belong to System Administrators *****/
ATENTION: Not only SA and OEM are members of the System Administrators Server Role
Check why the following users are also System Administrators:
Users with SYSADM Privileges
------------------------------
BUILTIN\Administrators
NOTE: Login 'BUILTIN\Administrators' shoul be eliminated.
Take care before eliminate this BUILTIN Group.
/*******************************/
| Operating System Information |
/*******************************/
NOTE: The following users are Local Administrators on hostname
Account Name Type
------------------------------ --------
hostname\Administrator user
hostname\dbasql user
hostname\gestadm user
Domainname\Domain Admins group
Domainname\LocalServerAdministr group
But i dont know if this output is suficient.
Thanks and regards,
Jorge Mendes
Thanks a lot.
Regards,
Jorge Manuel Mendes -
Jorge
Presumably this is the output of some health-checking tool that already exists? Does sqldiag.exe provide this?
John
-
John,
This is the output of a lot of procedures that i've made at two years a go. I only tried when we've got SQL Server 6.5 and SQL Server 2000 and now i dont know if they function on SQLS 2k5 and what else of privileges should i check in SQLS2k5
Thanks and regards,
Jorge Mendes
Thanks a lot.
Regards,
Jorge Manuel Mendes -
Jorge
I think you need to study the SQL Server 2005 security model - there's plenty of material in Books Online. Good luck!
Also, I would try out sqldiag, which is part of SQL Server, and SQLH2, which you can download from Microsoft, if I remember correctly.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply