Tag Archives: group

How to grant a User SELECT permission on multiple tables in SQL Server using T-SQL and Excel Formulas

Maybe you get emails from time to time saying something like “Hey can you grant so-and-so SELECT permission on” and then they list a few dozen tables.

There’s a couple of issues with this.

Firstly you shouldn’t be getting that as a simple email, it should come in as a formal access request.

Secondly User access should be defined in roles (or group logins if you want to manage access at an Active Directory level) that tie back to departments and seniority. Different roles have different permissions on different objects. This makes the subject of access more manageable and easily auditable. The access request should be “can you add so-and-so to this role” and ideally people should only exist in one role.

Thirdly this would be really annoying and, depending on the length of the table list, take too long to do via the SSMS GUI.

So if you are getting emails like the above try move your organisation along with regards the first two points. But to help you action the email I’ve created the Excel file DbaScripts_GrantSelect which can be downloaded here.

Grant Select Excel Sheet Snippet

The DbaScripts_GrantSelect file allows you to enter the Login (user name), Database name and Schema name in the first three columns. You can then copy and paste the table names into the fourth column called Table. Drag the first three columns down for as many table name entries there are. Then drag the SQL Command formula column down for as many table name entries there are and this will create the commands to grant SELECTs on the tables for the user specified.

If you can’t download the file above you can recreate it.

In an empty Excel sheet write the following into the cells as directed.

A1: Login
B1: Database
C1: Schema
D1: Table
E1: SQL Command

In E2 paste the following formula:

=”GRANT SELECT ON [“&B2&”].[“&C2&”].[“&D2&”] TO [“&A2&”];”

 

How to tell if you are a member of a SQL Server group or create a list of group members using T-SQL

The following scripts will help you determine if you are a member of a group or role or create a list of group members in SQL Server without having to use SQL Server Management Studio. This is a particularly handy script in determining who might have access to the server through Active Directory groups.

/*
The code below indicates whether the current user is a member 
of the specified Microsoft Windows group or SQL Server database role.
A result of 1 = yes
,0 = no
,null = the group or role queried is not valid.
*/

SELECT IS_MEMBER('[group or role]')


/*
The code below will create a list of all the logins that are members 
of a group.
*/

EXEC master..xp_logininfo 
@acctname = '[group]',
@option = 'members'

 

How to remove Logins and Users from SQL Server en masse

The script below will create SQL code to drop every user and login from a SQL Server instance. Not very useful unless you’re trying to give someone, possibly yourself, a very bad day. Don’t worry though the script does not execute the code, to reiterate it only generates it. However given the potential for you using the output of the script incorrectly this post comes with a disclaimer (Link to disclaimer).

To make the script a little more useful you can populate a temp table called #TheseUsersOnly in the script with the specific login/user names you want to remove.

This will limit the code outputted to only the logins and users specified.

Go to the /*INSERT LOGINS HERE*/ section to populate the temp table with hard coded login/user names.

Be sure to thoroughly review the code outputted before executing it.

If you encounter this error message “The database principal owns a schema in the database, and cannot be dropped“, see this post (Link).

SET NOCOUNT ON;

/*DECLARE VARIABLES*/
DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @DbName AS SYSNAME;
DECLARE @sqlCommand AS VARCHAR(MAX);
DECLARE @UserName AS VARCHAR(128);
DECLARE @i AS INT;
DECLARE @z AS INT;
DECLARE @j AS INT;
DECLARE @y AS INT;

/*DROP EXISTING TEMP TABLES*/
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
BEGIN
	DROP TABLE #ErrorTable
END;

IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
	DROP TABLE #User
END;

IF OBJECT_ID(N'tempdb..#UserOrdered') IS NOT NULL
BEGIN
	DROP TABLE #UserOrdered
END;

IF OBJECT_ID(N'tempdb..#TheseUsersOnly') IS NOT NULL
BEGIN
	DROP TABLE #TheseUsersOnly
END;

IF OBJECT_ID('tempdb..#Return') IS NOT NULL
BEGIN
	DROP TABLE #Return
END;

/*CREATE TEMP TABLES*/
/*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

CREATE TABLE #User (
	Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,DbName SYSNAME NULL
	,UserName SYSNAME NULL
	);

CREATE TABLE #UserOrdered (
	Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,DbName SYSNAME NULL
	,UserName SYSNAME NULL
	);

CREATE TABLE #TheseUsersOnly (UserName SYSNAME NULL);

CREATE TABLE [#Return] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

/*INSERT LOGINS HERE*/
/*
/*Single Login*/
INSERT INTO #TheseUsersOnly (UserName)
SELECT 'Test_Login';
*/
/*
/*Multiple Logins*/
INSERT INTO #TheseUsersOnly (UserName)
VALUES (Test_Login_1)
,(Test_Login_2)
,(Test_Login_3)
*/
SET @DbName = '';

/*GENERATE LIST TABLE OF DATABASE NAMES*/
INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC;

/*GENERATE LIST OF USERS FOR EACH DATABASE*/
WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			)
	SET @sqlCommand = '
INSERT INTO #User (
DbName
,UserName
)
SELECT ' + '''' + @DbName + '''' + ' AS DbName 
,princ.name AS UserName
FROM ' + QUOTENAME(@DbName) + '.sys.database_principals AS princ
WHERE princ.name IS NOT NULL
';

	/*OPTION TO LIMIT USERS TO ONLY THE USERS SPECIFIED*/
	IF EXISTS (
			SELECT *
			FROM #TheseUsersOnly
			)
	BEGIN
		SET @sqlCommand = @sqlCommand + 'AND princ.Name IN (SELECT UserName FROM #TheseUsersOnly);'
	END
	ELSE
	BEGIN
		SET @sqlCommand = @sqlCommand + ';'
	END

	/*ERROR HANDLING*/
	BEGIN TRY
		EXEC (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH
END;

/*REORDER USER TABLE BY NAME ALPHABETICALLY ASCENDING*/
INSERT INTO #UserOrdered (
	DbName
	,UserName
	)
SELECT DbName
	,UserName
FROM #User
ORDER BY UserName ASC
	,DbName ASC;

/*SET LOOP VARIABLES*/
SET @y = 0;
SET @i = 1;
SET @z = (
		SELECT COUNT(*)
		FROM #UserOrdered
		);

/*LOOP TO GENERATE SQL CODE*/
WHILE @i <= @z
BEGIN
	SELECT @DbName = DbName
		,@UserName = UserName
	FROM #UserOrdered
	WHERE Id = @i

	IF @y = 0
		SET @j = 1;

	BEGIN
		SET @y = (
				SELECT COUNT(UserName)
				FROM #UserOrdered
				WHERE UserName = @UserName
				)
	END

	SET @sqlCommand = 'USE [' + @DbName + '];
IF EXISTS (SELECT * FROM ' + QUOTENAME(@DbName) + '.sys.database_principals WHERE name = N' + '''' + @UserName + '''' + ')
BEGIN DROP USER [' + @UserName + '] END;
';

	/*ERROR HANDLING*/
	BEGIN TRY
		INSERT INTO #Return
		SELECT (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	IF @j = @y
	BEGIN
		IF EXISTS (
				SELECT NAME
				FROM master.sys.server_principals
				WHERE NAME = @UserName
				)
		BEGIN
			INSERT INTO #Return (SqlCommand)
			SELECT 'USE [master];
IF EXISTS (SELECT NAME
FROM master.sys.server_principals
WHERE NAME = ' + '''' + @UserName + '''' + '
) BEGIN
DROP LOGIN [' + @UserName + '] END;'
		END

		SET @y = 0;
	END

	SET @i = @i + 1;
	SET @j = @j + 1;
END

/*RESULTS*/
SELECT SqlCommand
FROM #ErrorTable;

SELECT SqlCommand
FROM #Return;

/*HOUSEKEEPING*/
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
BEGIN
	DROP TABLE #ErrorTable
END;

IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
	DROP TABLE #User
END;

IF OBJECT_ID(N'tempdb..#UserOrdered') IS NOT NULL
BEGIN
	DROP TABLE #UserOrdered
END;

IF OBJECT_ID(N'tempdb..#TheseUsersOnly') IS NOT NULL
BEGIN
	DROP TABLE #TheseUsersOnly
END;

IF OBJECT_ID('tempdb..#Return') IS NOT NULL
BEGIN
	DROP TABLE #Return
END;