March 31, 2010 at 4:23 am
hi,
I need to get the Accssible databases for particular user & his permissions for those DBs.
I need Urgent...
Thanks,
Sasidhar P
March 31, 2010 at 5:04 am
Sasidhar Pulivarthi (3/31/2010)
I need to get the Accssible databases for particular user & his permissions for those DBs.
Seems like unmatured post . give more details
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 31, 2010 at 5:18 am
DBNAme UserName dbo dbREad dbWrite............
MDEL sa yes yes yes
TEST tst Yes yes no
TEST dev Yes yes yes
I need the above one.......
also i would like to know that how to get the following table
DBobject UserName Read Write Execute
tbl1 tst1 Y N Y
sp1 tst1
view1 tst1
Thanks,
Sasidhar P
March 31, 2010 at 5:36 am
Sasidhar Pulivarthi (3/31/2010)
DBobject UserName Read Write Executetbl1 tst1 Y N Y
sp1 tst1
view1 tst1
try this -- =============================================
-- Author: Alejandro Pelc
-- Create date: 02/19/2009
-- Description: List all DBs permission
--
-- =============================================
set nocount on
declare @permission table (
Database_Name sysname,
User_Role_Name sysname,
Account_Type nvarchar(60),
Action_Type nvarchar(128),
Permission nvarchar(60),
ObjectName sysname null,
Object_Type nvarchar(60)
)
declare @dbs table (dbname sysname)
declare @Next sysname
insert into @dbs
select name from sys.databases order by name
select top 1 @Next = dbname from @dbs
while (@@rowcount<>0)
begin
insert into @permission
exec('use [' + @Next + ']
declare @objects table (obj_id int, obj_type char(2))
insert into @objects
select id, xtype from master.sys.sysobjects
insert into @objects
select object_id, type from sys.objects
SELECT ''' + @Next + ''', a.name as ''User or Role Name'', a.type_desc as ''Account Type'',
d.permission_name as ''Type of Permission'', d.state_desc as ''State of Permission'',
OBJECT_SCHEMA_NAME(d.major_id) + ''.'' + object_name(d.major_id) as ''Object Name'',
case e.obj_type
when ''AF'' then ''Aggregate function (CLR)''
when ''C'' then ''CHECK constraint''
when ''D'' then ''DEFAULT (constraint or stand-alone)''
when ''F'' then ''FOREIGN KEY constraint''
when ''PK'' then ''PRIMARY KEY constraint''
when ''P'' then ''SQL stored procedure''
when ''PC'' then ''Assembly (CLR) stored procedure''
when ''FN'' then ''SQL scalar function''
when ''FS'' then ''Assembly (CLR) scalar function''
when ''FT'' then ''Assembly (CLR) table-valued function''
when ''R'' then ''Rule (old-style, stand-alone)''
when ''RF'' then ''Replication-filter-procedure''
when ''S'' then ''System base table''
when ''SN'' then ''Synonym''
when ''SQ'' then ''Service queue''
when ''TA'' then ''Assembly (CLR) DML trigger''
when ''TR'' then ''SQL DML trigger''
when ''IF'' then ''SQL inline table-valued function''
when ''TF'' then ''SQL table-valued-function''
when ''U'' then ''Table (user-defined)''
when ''UQ'' then ''UNIQUE constraint''
when ''V'' then ''View''
when ''X'' then ''Extended stored procedure''
when ''IT'' then ''Internal table''
end as ''Object Type''
FROM [' + @Next + '].sys.database_principals a
left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id
left join @objects e on d.major_id = e.obj_id
order by a.name, d.class_desc')
delete @dbs where dbname = @Next
select top 1 @Next = dbname from @dbs
end
set nocount off
select * from @permission where database_name = 'yourDB'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 31, 2010 at 6:10 am
Sasidhar Pulivarthi (3/31/2010)
DBNAme UserName dbo dbREad dbWrite............MDEL sa yes yes yes
TEST tst Yes yes no
TEST dev Yes yes yes
try this USE master
GO
SET NOCOUNT ON
DECLARE @loginName sysname
SET @loginName = '%'
-- Retrieve DB Role Level Info
DECLARE @DBRolePermissions TABLE(
DatabaseName varchar(300),
Principal_Name sysname,
Login_Name sysname NULL,
DB_RoleMember varchar(300),
Permission_Type sysname)
INSERT INTO @DBRolePermissions
EXEC sp_MSforeachdb '
SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name,
roles.Name AS Role_Member_Name, roles.type_desc
FROM [?].sys.database_role_members r
LEFT OUTER JOIN [?].sys.database_principals users on r.member_principal_id = users.principal_id
LEFT OUTER JOIN [?].sys.database_principals roles on r.role_principal_id = roles.principal_id
--WHERE users.type not in (''R'')'
-- Capture permissions generated FROM sys.database_permissions
INSERT INTO @DBRolePermissions
EXEC sp_msforeachdb '
SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name,
r.Permission_Name AS DB_RoleMember, r.class_desc
FROM [?].sys.database_permissions r
LEFT OUTER JOIN [?].sys.database_principals users on r.Grantee_principal_id = users.principal_id
WHERE r.class_desc = ''DATABASE'''
SELECT DISTINCT Principal_Name, Login_Name, DatabaseName, DB_RoleMember AS Permission_Name, Permission_Type
FROM @DBRolePermissions
WHERE (ISNULL(Login_Name, '') LIKE @loginName OR ISNULL(Principal_Name, '') LIKE @loginName)
AND DatabaseName = 'yourDb'
ORDER BY Principal_Name, DatabaseName, DB_RoleMember
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 31, 2010 at 7:54 am
From my previous post fetch data into permanant table DBRolePermissions from @DBRolePermissions
and then use SELECT Principal_Name,
CASE WHEN [CONNECT] IS NULL THEN 0 ELSE 1 END [CONNECT],
CASE WHEN [db_datareader] IS NULL THEN 0 ELSE 1 END [db_datareader],
CASE WHEN [db_datawriter] IS NULL THEN 0 ELSE 1 END [db_datawriter],
CASE WHEN [db_owner] IS NULL THEN 0 ELSE 1 END [db_owner],
CASE WHEN [db_accessadmin] IS NULL THEN 0 ELSE 1 END [db_accessadmin]
FROM ( SELECT id
,[Permission_Name]
, Principal_Name
FROM DBRolePermissions
) p PIVOT ( max(id)
FOR [Permission_Name] IN ([CONNECT],[db_datareader],[db_datawriter],[db_owner],[db_accessadmin])
) AS pvt
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply