February 23, 2014 at 9:09 pm
i need to get the audit data for all the server and database roles to all the users in sql 2005 and 2008 , i have a scropt when i exedcuted geeting the error as
msg 2702 level 16 state 2 line 1 database 'tempdb' does not exist.
previously it worked in another server . may i know what is the reason for this .
Also any one provide the script to get this report.
February 24, 2014 at 12:18 am
Without seeing the script, no idea.
What report are you asking about the script for?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2014 at 12:36 am
--#### LOGINS,USERS,ROLES (LUR) SCRIPT #### --#### SCRIPT TESTED IN SQL SERVER 2000,2005,2008 AND 2008R2####
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
-- CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLES
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES](
[DBNAME] [SYSNAME] ,
[USERNAME] [SYSNAME] ,
[DB_OWNER] [VARCHAR](3) ,
[DB_ACCESSADMIN] [VARCHAR](3) ,
[DB_SECURITYADMIN] [VARCHAR](3) ,
[DB_DDLADMIN] [VARCHAR](3) ,
[DB_DATAREADER] [VARCHAR](3) ,
[DB_DATAWRITER] [VARCHAR](3) ,
[DB_DENYDATAREADER] [VARCHAR](3) ,
[DB_DENYDATAWRITER] [VARCHAR](3) ,
[DT_CREATE] [DATETIME] NOT NULL,
[DT_UPDATE] [DATETIME] NOT NULL,
[DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3] DEFAULT (GETDATE()) )
ON [PRIMARY]
GO
INSERT INTO [TEMPDB].[DBO].[DB_ROLES]
EXEC SP_MSFOREACHDB
' SELECT
''?'' AS DBNAME,
USERNAME,
MAX(CASE ROLENAME WHEN ''DB_OWNER'' THEN ''YES'' ELSE ''NO'' END) AS DB_OWNER,
MAX(CASE ROLENAME WHEN ''DB_ACCESSADMIN '' THEN ''YES'' ELSE ''NO'' END) AS DB_ACCESSADMIN ,
MAX(CASE ROLENAME WHEN ''DB_SECURITYADMIN'' THEN ''YES'' ELSE ''NO'' END) AS DB_SECURITYADMIN,
MAX(CASE ROLENAME WHEN ''DB_DDLADMIN'' THEN ''YES'' ELSE ''NO'' END) AS DB_DDLADMIN,
MAX(CASE ROLENAME WHEN ''DB_DATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DATAREADER,
MAX(CASE ROLENAME WHEN ''DB_DATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DATAWRITER,
MAX(CASE ROLENAME WHEN ''DB_DENYDATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAREADER,
MAX(CASE ROLENAME WHEN ''DB_DENYDATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAWRITER,
CREATEDATE,
UPDATEDATE,
GETDATE()
FROM (
SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE
FROM
[?].DBO.SYSMEMBERS A JOIN [?].DBO.SYSUSERS B ON A.MEMBERUID = B.UID JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID )S
GROUP BY USERNAME, CREATEDATE, UPDATEDATE
ORDER BY USERNAME'
-- RETRIVE LOGINS, USERS AND ROLES TOGETHER
SELECT SERVERPROPERTY('SERVERNAME') AS [SERVERNAME],
B.NAME AS [LOGINNAME],
CASE B.SYSADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SYSADMIN,
CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SECURITYADMIN,
CASE B.SETUPADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SETUPADMIN,
CASE B.PROCESSADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS PROCESSADMIN,
CASE B.DISKADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS DISKADMIN,
CASE B.DBCREATOR WHEN '1' THEN 'YES' ELSE 'NO' END AS DBCREATOR,
CASE B.BULKADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS BULKADMIN,
B.DBNAME AS [DEFAULT_DBNAME],
A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES] A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME
--WHERE B.ISNTUSER=1
--INCLUDE TO EXCLUDE THE SQL LOGINS
SELECT * FROM #LOGINS ORDER BY [LOGINNAME]
DROP TABLE [TEMPDB].[DBO].[DB_ROLES]
DROP TABLE #LOGINS
this is the script which iam executing ..
February 24, 2014 at 12:42 am
Ok, and what statement is failing where?
Does the user executing that have permissions to create explicit tables in TempDB? Why permanent tables and not temp tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2014 at 12:48 am
its getting error when creating the table
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]
getting error
Msg 2702, Level 16, State 2, Line 1
Database 'TEMPDB' does not exist.
February 24, 2014 at 12:50 am
GilaMonster (2/24/2014)
Does the user executing that have permissions to create explicit tables in TempDB? Why permanent tables and not temp tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2014 at 1:51 am
i have sysadmin permissions on the server , incase if it is not worked can you pls provide the script to get the mentioned data as per the above i.e server roles\database roles of all the users .
February 24, 2014 at 1:56 am
Try changing the permanent table to a temp table, I can't see any valid reason why that script would be creating a permanent table in tempDb instead of just using a temp table.
Only other thing I can think of is that your server is case-sensitive and there's a difference in case between the script and the actual database. Check and see if that is the case.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2014 at 3:29 am
can you please provide the syntax for this ?
February 24, 2014 at 4:17 am
For what, creating a temp table? http://technet.microsoft.com/en-us/library/ms174979.aspx Scroll down to the Remarks section and look for the header "Temporary Tables"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2014 at 7:24 am
GilaMonster (2/24/2014)
Try changing the permanent table to a temp table, I can't see any valid reason why that script would be creating a permanent table in tempDb instead of just using a temp table.
I was thinking the same thing, why not create the dbroles table as a permanent table since that's the table that will be holding all the user login's by date, user type, etc...
February 24, 2014 at 7:29 am
Appears this question has been reposted elsewhere. Further replies to the following thread please www.sqlservercentral.com/Forums/Topic1544461-391-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2014 at 12:56 pm
ramyours2003 (2/24/2014)
...
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES](
...
this is the script which iam executing ..
Make sure your server isn't using a case sensitive collation, if it is, you might want to use lowercase for 'tempdb' and 'dbo.'
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply