Sql query to show permissions

I have the following sql script that when running on one of my servers runs correctly. However, on my second server I have two databases offline so I receive the following error:

Msg 942, Level 14, State 4, Line 1
Database 'Test' cannot be opened because it is offline.

My question is what needs to be inserted (and where) to only run and check databases that are online? The script is as follows:

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
DJ PAsked:
Who is Participating?
 
Doug WaltonConnect With a Mentor Database AdministratorCommented:
In your line
select name from sys.databases order by name

You can add a where clause to check the state.  This will only include online databases:
select name from sys.databases where state=0 order by name

Open in new window


https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql

State values can be:
Value | Applies to
0 = ONLINE
1 = RESTORING
2 = RECOVERING : SQL Server 2008 through SQL Server 2017
3 = RECOVERY_PENDING : SQL Server 2008 through SQL Server 2017
4 = SUSPECT
5 = EMERGENCY : SQL Server 2008 through SQL Server 2017
6 = OFFLINE : SQL Server 2008 through SQL Server 2017
7 = COPYING : Azure SQL Database Active Geo-Replication
10 = OFFLINE_SECONDARY : Azure SQL Database Active Geo-Replication

Hope this helps!
0
 
Garfield SamuelsProject ManagerCommented:
Based on the error, you may need to do is get your DB online before you can properly run your query properly .  

I am assuming you are using MSSQL.  
If yes, then go into MS SQL Management Studio and browse to your DB.
Right click on the DB and select [ Tasks ]  >  [ Bring  Online ]

Once is the DB is online you can try running your query again.
0
 
DJ PAuthor Commented:
Awesome! Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.