Re-generating SQL Server Logins, Part 2

This is the second article on my Re-generate SQL Server
login series. In my first
column
, I focused on server-level role and database-level role
assignment for any assigned login. I also mentioned frequent DBA encountered
tasks related to ensuring login properties setup and what kind of implications might
be created if login permission is not properly defined. In this portion of the
series, I’d like to walk through list of securables
and how to generate login to include securable granting and denying.

What is a securable?

Simply put, any entity within the database engine that
can be secured with permission is called securable. The highest levels of securables are servers and databases, mid-level securables are assemblies, full-text catalogs, message
types, etc., and the finest level securables are
table, view, function, stored procedure, etc. We can grant, deny and revoke
permission on securables.

Server-level permission

Below is the list of server-level permissions. The list
on the left also includes the permission defined on the right. For instance,
once we grant a login control server permission, the
login will also have privilege to do bulk operation, create and alter any
database, create unsafe assembly and shutdown the SQL instance. We need to be
extra careful when granting anyone server-level permission. One thing worth
mentioning is that by default all users have permission to "VIEW ANY
DATABASE." Also, to grant server-level permission, we need to be in the master
database to issue the server scope granting command and we either need to be a sysadmin or possess the server-level permission and higher
with GRANT option to give others permission on server level. The server-level
permission information is stored on sys.server_permissions.

Server Permission

Finer Server Permission

CONTROL SERVER

ADMINISTER BULK OPERATIONS

CONTROL SERVER

ALTER ANY CONNECTION

CONTROL SERVER

ALTER ANY CREDENTIAL

CONTROL SERVER

ALTER ANY DATABASE

CONTROL SERVER

ALTER ANY ENDPOINT

CONTROL SERVER

ALTER ANY EVENT NOTIFICATION

CONTROL SERVER

ALTER ANY LINKED SERVER

CONTROL SERVER

ALTER ANY LOGIN

CONTROL SERVER

ALTER ANY SERVER AUDIT

CONTROL SERVER

ALTER RESOURCES

CONTROL SERVER

ALTER SERVER STATE

CONTROL SERVER

ALTER SETTINGS

CONTROL SERVER

ALTER TRACE

CONTROL SERVER

AUTHENTICATE SERVER

CONTROL SERVER

CONNECT SQL

CONTROL SERVER

CONTROL SERVER

CONTROL SERVER

EXTERNAL ACCESS ASSEMBLY

CONTROL SERVER

SHUTDOWN

CONTROL SERVER

UNSAFE ASSEMBLY

CONTROL SERVER

VIEW ANY DEFINITION

ALTER ANY EVENT NOTIFICATION

CREATE DDL EVENT NOTIFICATION

ALTER ANY EVENT NOTIFICATION

CREATE TRACE EVENT NOTIFICATION

ALTER ANY ENDPOINT

CREATE ENDPOINT

ALTER SERVER STATE

VIEW SERVER STATE

ALTER ANY DATABASE

CREATE ANY DATABASE

VIEW ANY DEFINITION

VIEW ANY DATABASE

Permission on database-level securable

Unlike server-level permission that you can only define
when connecting to the master database, each database has its list of
pre-defined class of securable that you can grant permission on database
principal (database user). Here is the list of securable class on which
permission exists:

  • Database
  • Object or column
  • Schema
  • Database principal
  • Assembly
  • Type
  • XML schema collection
  • Message type (Service Broker securable)
  • Service contract (Service Broker securable)
  • Service (Service Broker securable)
  • Remote Service Binding (Service Broker
    securable)

  • Route (Service Broker securable)
  • Full-Text Catalog
  • Symmetric Key
  • Certificate
  • Asymmetric Key

Database-level Permission information is stored on sys.database_permissions. Unless sys.server_permissions, sys.database_permissions
exists for every database.

Grant, revoke and deny

The definition for GRANT, REVOKE and DENY can be
confusing sometimes. Simply put, revoke is used to remove the permission that
has been GRANTED or DENIED on the same securable.

But what if we grant permission to a lower scope
securable and then issue DENY or REVOKE on a higher scope securable, what will
happen to the lower scope securable? No surprise, there is a difference.

When Denying on a higher-level securable, the principal
will be denied on the lower-level securable.

When Revoking on a higher-level securable, the
principal will keep his granted permission on the lower scope securable.

A principal can grant permission to another principal
if his permission is granted with grant option.

Generate script for server and database securable class

We have briefly gone over the available list of server-level
and database-level securable class. We often face tasks like listing out what
permission a login has. In my first article in this series, we talked about
server role and database role assignment for any given login. But there are
cases where login was given more specific permission that was not given through
server or database role but through server and database securable. This is the
most easily forgotten part of login permission.

It would be nice to simply input the login name that we are interested in and get ready
to use script to recover permission on server level and database level
securable for the specified principal. In this article, I am only focusing on
database and server-level securables. If you are
interested to see how to generate login creation, server role assignment or
database role assignment, please refer to the first article of the series.

Now, we can look into the script. To use the script, here are the steps. This script
works on SQL 2005, SQL 2008 and SQL 2008 R2.

1.    Open Microsoft SQL
Server Management Studio (a.k.a SSMS), make a
connection to the SQL instance, then click New Query.
Make sure the credential you use to run the code has adequate privileges to
generate the script.

2.    Copy below code
into the query window

3.    Replace ‘YourloginName’ with the login you
would like to re-generate securable permission script, then execute

 

———————————————-

–Login Pre-requisites

———————————————-

set concat_null_yields_null off

USE master

go

SET NOCOUNT ON

DECLARE @login_name varchar(100)

SET @login_name = ‘YourLoginName’

 

—————————————————————–

 

IF lower(@login_name) IN (‘sa’,‘public’)

BEGIN

          RAISERROR (15405,11,1,@login_name)

          RETURN

END

 

 

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name =
@login_name AND type
IN (‘G’,‘U’,‘S’))

BEGIN

          PRINT ‘Please input valid
login name’

          RETURN

END

 

 

DECLARE @login_sid varbinary(85)

SELECT @login_sid = sid FROM sys.server_principals WHERE
name = @login_name

 

DECLARE @maxid int

 

IF OBJECT_ID(‘tempdb..#db_users’) is not null

BEGIN

          DROP TABLE #db_users

END

 

SELECT id = identity(int,1,1), sql_cmd = ‘SELECT ”’+name+”’, * FROM [’+name+‘].sys.database_principals’ INTO
#db_users FROM sys.databases

WHERE state_desc <> ‘OFFLINE’

 

SELECT @maxid = @@ROWCOUNT

 

 

———————————————-

–Create Server Role Temp table

———————————————-

 

IF OBJECT_ID(‘tempdb..#srvrole’) IS NOT NULL

BEGIN

          DROP TABLE #srvrole

END

 

CREATE TABLE #srvrole(ServerRole sysname, MemberName sysname, MemberSID varbinary(85)) 

INSERT INTO [#srvrole] EXEC sp_helpsrvrolemember

 

DECLARE @login_srvrole varchar(1000)

SET @login_srvrole =

IF EXISTS (select * from [#srvrole] where
ServerRole = ‘sysadmin’
AND MemberName =
@login_name)

BEGIN

         

          PRINT ‘–Login [’+@login_name+‘] is part of sysadmin server role, hence possesses full
privileges for SQL instance: ‘
+@@servername

          PRINT ‘GO’

          SELECT @login_srvrole =
@login_srvrole + ‘EXEC
sp_addsrvrolemember ”’
+MemberName+”’,”’+ServerRole+””+CHAR(10) FROM #srvrole

          WHERE [MemberName] =
@login_name

          PRINT @login_srvrole

          RETURN

          RETURN

END

 

—————————————————

–Find out list of db that the
login has access to

—————————————————

 

PRINT

PRINT ‘———————————————-‘

PRINT ‘–Create database user for login ‘

PRINT ‘———————————————-‘

 

IF OBJECT_ID(‘tempdb..#alldb_users’) is not null

BEGIN

          DROP TABLE
#alldb_users

END

 

CREATE TABLE #alldb_users(

          [dbname]
[sysname] NOT NULL,

          [name]
[sysname] NOT NULL,

          [principal_id]
[int] NOT NULL,

          [type]
[char](1) NOT NULL,

          [type_desc]
[nvarchar](60) NULL,

          [default_schema_name]
[sysname] NULL,

          [create_date]
[datetime] NOT NULL,

          [modify_date]
[datetime] NOT NULL,

          [owning_principal_id]
[int] NULL,

          [sid]
[varbinary](85)
NULL,

          [is_fixed_role]
[bit] NOT NULL

)

 

DECLARE @id int, @sqlcmd varchar(500)

SET @id = 1

WHILE @id <=@maxid

BEGIN

          SELECT @sqlcmd =
sql_cmd FROM #db_users WHERE
id = @id

          INSERT INTO
#alldb_users EXEC (@sqlcmd)

          SET @id = @id + 1

END

 

 

DELETE FROM #alldb_users WHERE sid is null

 

DELETE FROM #alldb_users WHERE sid <> @login_sid

 

IF NOT EXISTS (SELECT * FROM #alldb_users )

BEGIN

          PRINT ‘–Login [’+@login_name+‘] doesnt have access to any database’

END

 

DECLARE @name sysname, @dbname sysname, @schema sysname, @dbuser_cmd varchar(8000)

DECLARE dbuser_cursor CURSOR
FAST_FORWARD FOR

SELECT dbname, name, default_schema_name

FROM #alldb_users

 

OPEN dbuser_cursor

FETCH NEXT FROM dbuser_cursor INTO @dbname, @name, @schema

WHILE @@FETCH_STATUS = 0

BEGIN

 

IF @schema IS NOT NULL

BEGIN    

SELECT @dbuser_cmd = ‘USE [’+dbname+‘]

IF NOT EXISTS (SELECT * FROM
sys.database_principals WHERE name = ”’
+name+”’)

BEGIN

          CREATE
USER [’
+@name+‘] FOR LOGIN [’+@login_name+‘]’+isnull(‘ WITH DEFAULT_SCHEMA=[’+default_schema_name+‘]’,)+

END

GO

FROM #alldb_users WHERE name = @name and dbname = @dbname

END

ELSE

BEGIN

SELECT @dbuser_cmd = ‘USE [’+dbname+‘]

IF NOT EXISTS (SELECT * FROM
sys.database_principals WHERE name = ”’
+name+”’)

BEGIN

          CREATE
USER [’
+@name+‘] FOR LOGIN [’+@login_name+‘]

END

GO

FROM #alldb_users WHERE name = @name and dbname = @dbname

END

 

          print @dbuser_cmd

    FETCH NEXT FROM dbuser_cursor INTO
@dbname, @name,
@schema

    END

 

CLOSE dbuser_cursor

DEALLOCATE dbuser_cursor

 

———————————————-

–Create DB Role Temp table

———————————————-

 

IF OBJECT_ID(‘tempdb..#dbrole’) is not null

BEGIN

          DROP TABLE #dbrole

END

 

create table #dbrole (id int identity(1,1), dbname varchar(100), dbrole varchar (100), dbrole_member varchar(100), sid varbinary(85),
default_schema_name varchar(100), login_name varchar(100), db_principal_id int)

DECLARE @dbrole_sqlcmd varchar(max)

DECLARE dbrole_cursor CURSOR
FAST_FORWARD FOR

SELECT

‘SELECT ”’+dbname+”’, c.name, b.name, b.sid, b.default_schema_name, d.name,
b.principal_id as login_name

from [’+dbname+‘].sys.database_role_members
a

inner join [’+dbname+‘].sys.database_principals b on a.member_principal_id =
b.principal_id

inner join [’+dbname+‘].sys.database_principals c on a.role_principal_id =
c.principal_id

left join sys.server_principals d on
b.sid = d.sid

where d.name= ”’+@login_name+””

from #alldb_users

 

OPEN dbrole_cursor

FETCH NEXT FROM dbrole_cursor INTO
@dbrole_sqlcmd

WHILE @@FETCH_STATUS = 0

BEGIN

   

    INSERT INTO #dbrole (dbname, dbrole,
dbrole_member, sid, default_schema_name,
login_name, db_principal_id) exec(@dbrole_sqlcmd)

    FETCH NEXT FROM dbrole_cursor INTO
@dbrole_sqlcmd

    END

 

CLOSE dbrole_cursor

DEALLOCATE dbrole_cursor

 

DELETE FROM #dbrole WHERE sid <> @login_sid

 

 

IF EXISTS (SELECT * FROM #dbrole where
dbrole = ‘db_owner’)

BEGIN

          PRINT ‘———————————————-‘

          PRINT‘–Login is db_owner of
below databases’

          PRINT‘———————————————-‘

 

END

 

DECLARE @dbname_dbowner varchar(100), @dbrole_member
varchar(100)

DECLARE dbowner_cursor CURSOR
FAST_FORWARD FOR

SELECT dbname, dbrole_member from #dbrole where
dbrole = ‘db_owner’

 

OPEN dbowner_cursor

FETCH NEXT FROM dbowner_cursor INTO
@dbname_dbowner, @dbrole_member

WHILE @@FETCH_STATUS = 0

BEGIN

 

          PRINT ‘USE [’+@dbname_dbowner+‘]

EXEC sp_addrolemember ”db_owner”,”’+@dbrole_member +”’

GO’

    FETCH NEXT FROM dbowner_cursor INTO
@dbname_dbowner, @dbrole_member

    END

 

CLOSE dbowner_cursor

DEALLOCATE dbowner_cursor

 

 

——————————————————————————————————–

–Find out what database the login
has permission to access (avoid restricted and single user database)

——————————————————————————————————–

 

DELETE From #srvrole where MemberName <>
@login_name

 

IF OBJECT_ID(‘tempdb..#alldb_users_access’) IS NOT NULL

BEGIN

          DROP TABLE
#alldb_users_access

END

 

SELECT a.*, collation_name INTO #alldb_users_access FROM
#alldb_users a inner join
sys.databases b ON a.dbname = b.name

WHERE user_access = 0

OR

(user_access = 2 and exists (SELECT * FROM #srvrole WHERE
ServerRole in (‘dbcreator’,‘sysadmin’)))

OR

(user_access = 2 and a.dbname in (SELECT dbname FROM #dbrole WHERE
dbrole = ‘db_owner’
AND login_name =
@login_name))

 

——————————————————————————————————–

–Remove database that login doesnt
have permission to connect

——————————————————————————————————–

 

IF OBJECT_ID(‘tempdb..#dbconnect’) is not null

BEGIN

          DROP TABLE #dbconnect

END

 

CREATE TABLE #dbconnect ( dbname varchar(100), connect_status bit)

 

DECLARE @dbconnect_sqlcmd varchar(1000)

SET @dbconnect_sqlcmd  =

DECLARE dbbconnect_cursor CURSOR
FAST_FORWARD FOR

SELECT ‘select distinct ”’+dbname+”’, 1 from [’+dbname+‘].sys.database_permissions
a

inner join [’+dbname+‘].sys.database_principals b on a.grantee_principal_id =
b.principal_id

inner join [’+dbname+‘].sys.server_principals c on b.sid = c.sid

where c.name = ”’+@login_name+””

from #alldb_users_access

 

OPEN dbbconnect_cursor

FETCH NEXT FROM dbbconnect_cursor INTO
@dbconnect_sqlcmd

WHILE @@FETCH_STATUS = 0

BEGIN

 

          INSERT INTO
#dbconnect exec(
@dbconnect_sqlcmd)

    FETCH NEXT FROM dbbconnect_cursor INTO
@dbconnect_sqlcmd

    END

 

CLOSE dbbconnect_cursor

DEALLOCATE dbbconnect_cursor

 

insert into #dbconnect

select a.dbname, 0 from
#alldb_users_access a left join #dbconnect b on
a.dbname = b.dbname

where b.dbname is null

 

—————————————————————————————

                                                       Grant
all securable class to login

—————————————————————————————-

PRINT

PRINT ‘———————————————-‘

PRINT ‘–Grant all securable class to
login ‘

PRINT ‘———————————————-‘

 

 

IF OBJECT_ID(‘tempdb..#securable_class’) is not null

BEGIN

          DROP TABLE
#securable_class

END

 

IF OBJECT_ID(‘tempdb..#dblevel’) is not null

BEGIN

          DROP TABLE #dblevel

END

create table #dblevel (login_name varchar(256), dbname sysname, dbuser_name varchar(100), class_desc varchar(100), permission_name varchar(100), state_desc varchar(100))

 

DECLARE @dblevel_sqlcmd varchar(1000)

DECLARE dblevel_cursor CURSOR
FAST_FORWARD FOR

SELECT ‘select ”’+@login_name+”’ as login_name, ”’+dbname+”’ as dbname, b.name as
dbuser_name, a.class_desc, a.permission_name, state_desc from [’
+dbname+‘].sys.database_permissions a inner join [’+dbname+‘].sys.database_principals b

on a.grantee_principal_id =
b.principal_id

where b.name in (”public”,”’+name+”’)
and class_desc = ”DATABASE”’

FROM #alldb_users_access

union

SELECT ‘select ”’+@login_name+”’ as login_name, ”master” as dbname, b.name as
dbuser_name, a.class_desc, a.permission_name, state_desc from
sys.server_permissions a inner join sys.server_principals b

on a.grantee_principal_id =
b.principal_id

where b.name = ”’+@login_name+””

UNION

SELECT ‘select ”’+@login_name+”’ as login_name, ”master” as dbname, b.name as
dbuser_name, a.class_desc, a.permission_name, state_desc from
sys.server_permissions a inner join sys.server_principals b

on a.grantee_principal_id =
b.principal_id and class_desc = ”SERVER”

where b.name = ”public”’

 

OPEN dblevel_cursor

FETCH NEXT FROM dblevel_cursor INTO
@dblevel_sqlcmd

WHILE @@FETCH_STATUS = 0

BEGIN

   

    INSERT INTO #dblevel (login_name, dbname, dbuser_name, class_desc,
permission_name, state_desc) EXEC (@dblevel_sqlcmd)

    FETCH NEXT FROM dblevel_cursor INTO
@dblevel_sqlcmd

    END

 

CLOSE dblevel_cursor

DEALLOCATE dblevel_cursor

 

SET NOCOUNT ON

 

DELETE FROM #dblevel WHERE permission_name IN (‘SELECT’,‘INSERT’,‘UPDATE’,‘DELETE’,‘REFERENCES’)

DELETE FROM #dblevel WHERE dbname IN (SELECT dbname FROM
#dbrole WHERE sid
= @login_sid AND
dbrole = ‘db_owner’)

 

DECLARE @securable_sqlcmd varchar(150)

DECLARE securable_cursor CURSOR
FAST_FORWARD FOR

SELECT distinct ‘USE [’+dbname+‘]

GRANT ‘+permission_name+‘ TO [’+@login_name+‘]

GO

FROM #dblevel

 

OPEN securable_cursor

FETCH NEXT FROM securable_cursor INTO
@securable_sqlcmd

WHILE @@FETCH_STATUS = 0

BEGIN

          PRINT @securable_sqlcmd

          FETCH NEXT FROM securable_cursor INTO
@securable_sqlcmd

END

CLOSE securable_cursor

DEALLOCATE securable_cursor

 

If you input a bad login name to the script, it will
produce an exception; below are the scenarios.

Scenario 1: If you enter either ‘sa’ or
‘public’,
then you will see results similar to the below snapshot in the SSMS result
pane (Fig. 1.1).

Fig
1.1

Scenario 2: If the login you provide doesn’t exist on
the SQL instance, then you will see results similar to the below snapshot in
the SSMS result pane (Fig 1.2)

Fig 1.2

Now we can look into the real scenario about a valid
login with permissions on server and database-level securables.
By replacing ‘YourLoginName’ with a valid login,
below is an example of how the outcome of the script will look. It contains the
creation of the database user for the login and the permission setting for
server and database securables. If a login is db_owner for any database or is part of sysadmin
server role member, then the resulting script will only contain the execution
of system stored procedure sp_addrolemember and sp_addsrvrolemember
instead of printing out granting securables one by
one.

Fig
1.3

Scenario 4: Input login is member of db_owner on
AdventureWorksDW, not for the rest of the databases (Fig 1.4)

———————————————-

–Create database user for login

———————————————-

USE [master]

IF NOT EXISTS (SELECT * FROM sys.database_principals
WHERE name = ‘chsu’)

BEGIN

          CREATE USER [chsu]
FOR LOGIN
[chsu] WITH DEFAULT_SCHEMA=[dbo]

END

GO

USE [AdventureWorks]

IF NOT EXISTS (SELECT * FROM sys.database_principals
WHERE name = ‘chsu’)

BEGIN

          CREATE USER [chsu]
FOR LOGIN
[chsu] WITH DEFAULT_SCHEMA=[dbo]

END

GO

USE [Northwind]

IF NOT EXISTS (SELECT * FROM sys.database_principals
WHERE name = ‘chsu’)

BEGIN

          CREATE USER [chsu]
FOR LOGIN
[chsu] WITH DEFAULT_SCHEMA=[dbo]

END

GO

USE [AdventureWorksDW]

IF NOT EXISTS (SELECT * FROM sys.database_principals
WHERE name = ‘chsu’)

BEGIN

          CREATE USER [chsu]
FOR LOGIN
[chsu] WITH DEFAULT_SCHEMA=[dbo]

END

GO

———————————————-

–Login is db_owner of below
databases

———————————————-

USE [AdventureWorksDW]

EXEC sp_addrolemember ‘db_owner’,‘user_dbo_bond_bg’

GO

———————————————-

–Grant all securable class to
login

———————————————-

USE [Northwind]

GRANT ALTER ANY APPLICATION ROLE TO [chsu]

GO

USE [Northwind]

GRANT ALTER ANY ROUTE TO [chsu]

GO

USE [Northwind]

GRANT CONNECT TO [chsu]

GO

USE [Northwind]

GRANT CREATE ROUTE TO [chsu]

GO

USE [Northwind]

GRANT SHOWPLAN TO [chsu]

GO

USE [master]

GRANT ALTER ANY LINKED SERVER TO [chsu]

GO

USE [master]

GRANT ALTER SETTINGS TO [chsu]

GO

USE [master]

GRANT CONNECT SQL TO [chsu]

GO

USE [master]

GRANT CONNECT TO [chsu]

GO

USE [master]

GRANT EXTERNAL ACCESS ASSEMBLY TO [chsu]

GO

USE [master]

GRANT UNSAFE ASSEMBLY TO [chsu]

GO

USE [master]

GRANT VIEW ANY DATABASE TO [chsu]

GO

USE [AdventureWorks]

GRANT CONNECT TO [chsu]

GO

USE [AdventureWorks]

GRANT SHOWPLAN TO [chsu]

GO

Fig 1.4

Conclusion

We have covered login regeneration in the first article with
login creation, server-level role and database-level role assignment. In this
artcile, we have summarized how to produce script for login on server-level and
database-level securables.

In my final article of the series, I’ll tackle the
login generation for the finest scope securable – object-level permission for
the login.

See all articles by Claire Hsu

Claire Hsu
Claire Hsu has nearly 10 years' experience in database administration, architecture, engineering and development, ranging from SQL 7.0, 2000, 2005 and 2008 environments. Currently, she is a senior DBA for a global infrastructure team in an investment management firm located in New York City. She is certified in MCP, OCP 10G, MCTS and MCITP on SQL Server 2005, and has a master's in Electrical Engineering from University of Massachusetts, Amherst. Her focus as DBA includes project management, handling critical projects like server upgrades, migration, high availability features deployment, performance tuning, monitoring, ensuring business continuity, and client facing and engineering automated processes.

Latest Articles