June 27, 2006 at 9:08 am
I need to know if my Sql Server Logins has a Server Role assigned and which of Sql database they have access.
Thanks for any help.
June 27, 2006 at 11:17 am
I don't know of a function to give you what you are looking for that already exists in SQL Server (not that it doen't exist, i just don't know about it... so here is what I do)
It returns a result set that contains the databases that a user has access to and the roles that the user has in each database.
-------------------------------------------------------------------------------------------
-- USER REPORT
-------------------------------------------------------------------------------------------
DECLARE @tblDatabases TABLE (iRowId INT IDENTITY(1,1),vcDatabase VARCHAR(255))
DECLARE @iRowId INT,@vcDatabase VARCHAR(255),@vcLoginName VARCHAR(255),@vcSQL VARCHAR(8000)
DECLARE @vcRoleName VARCHAR(255),@iRowCount INT
DECLARE @tblUserRoles TABLE(iRowId INT IDENTITY(1,1),vcLoginName VARCHAR(255),vcDatabase VARCHAR(255),vcRoleName VARCHAR(255))
SET NOCOUNT ON
CREATE TABLE #tblUserDbs (iRowId INT IDENTITY(1,1),vcLoginName VARCHAR(255), vcDatabase VARCHAR(255))
-----------------------------------------------------------------------
-- SET USER FOR REPORT HERE
-----------------------------------------------------------------------
SET @vcLoginName = USER -- Set to specific user
-----------------------------------------------------------------------
-- SET USER FOR REPORT HERE
-----------------------------------------------------------------------
INSERT INTO @tblDatabases Select name from master..sysdatabases
SET @iRowId = @@ROWCOUNT
WHILE @iRowId > 0
BEGIN
SET @vcDatabase = (Select vcDatabase from @tblDatabases where iRowId = @iRowId)
SET @vcSQL = 'select '''+@vcLoginName+''','''+@vcDatabase+''' from ['+@vcDatabase+'].[dbo].[sysusers] where name = '''+@vcLoginName+''' and hasdbaccess = 1'
INSERT INTO #tblUserDbs EXEC(@vcSQL)
SET @iRowId = @iRowId - 1
END
--Select vcLoginName,vcDatabase from #tblUserDbs
SET @iRowId = (Select count(*) from #tblUserDbs)
WHILE @iRowId > 0
BEGIN
SET @vcDatabase = (Select vcDatabase from #tblUserDbs where iRowId = @iRowId)
CREATE TABLE #tblRoles (iRowId INT IDENTITY(1,1),RoleName VARCHAR(255),RoleId INT,IsAppRole INT)
CREATE TABLE #tblMembers (iRowId INT IDENTITY(1,1),DbRole VARCHAR(255),MemberName VARCHAR(255),MemberSID BINARY(16))
INSERT INTO #tblRoles exec sp_helprole
SET @iRowCount = @@ROWCOUNT
WHILE @iRowCount > 0
BEGIN
TRUNCATE TABLE #tblMembers
SET @vcRoleName = (Select RoleName From #tblRoles Where iRowId = @iRowCount)
INSERT INTO #tblMembers EXEC sp_helprolemember @vcRoleName
INSERT INTO @tblUserRoles Select @vcLoginName,@vcDatabase,DbRole from #tblMembers where MemberName = @vcLoginName
SET @iRowCount = @iRowCount - 1
END
drop table #tblRoles
drop table #tblMembers
SET @iRowId = @iRowId - 1
END
select vcLoginName,vcDatabase,vcRoleName from @tblUserRoles
DROP TABLE #tblUserDBs
SET NOCOUNT OFF
--------------------------------------------------------------------------------------------------------
-
June 28, 2006 at 10:10 am
You can use sp_helplogins and sp_helpsrvrolemember or look in the Security folder in Enterprise Manager.
Greg
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply