Sql-server – Assign Permissions to a SQL User

permissionssql serversql server 2014users

I have active directory on my company, I have some login users created at the level of the SQL Server (2014) that I need to propagate to all my databases on that server. I want to create a read only user and also a read write user on sql server. So I created one user USER_R and another USER_RW, that match with the domain users, on my USER_R, I want to assign db_datareader and db_denydatawriter and then select all my dbs on the User Mapping section and click OK, this will create USER_R in all my dbs.

Question 1, how to automatically manage this in case I restore a new db on that server and I want to assign the user USER_R?

Question 2, now that I have the user USER_R, a user A want to connect to management studio, he doesn't see any table or stored procedure, what's missing to let them see all objects and besides be read only?

Best Answer

Question 1, how to automatically manage this in case I restore a new db on that server and I want to assign the user USER_R?

As described here you could add the user to the model database to automatically be added to other, created databases.

However, this will not work for restored databases.

USE [model]
GO
CREATE USER [testlogin] FOR LOGIN [testlogin]
GO
USE [model]
GO
ALTER ROLE [db_datareader] ADD MEMBER [testlogin]
GO

Restored databases are hard to track, more information on why life is hard in this article by Erik Darling.

Unfortunately, triggers won't directly work on restored databases.

If you where on SQL Server 2016, you could try and cook something up with an extended event on backup_restore_progress_trace, but that does not help you here.

In this case I would just add the user creation to your restore process.

Either with dynamic SQL if you have to

DECLARE @SQL nvarchar(max)
  SET @SQL =
   '
        USE ' + QUOTENAME(@RestoredDatabaseName) + '

IF NOT EXISTS (
SELECT * FROM sys.sysusers where name = ''testlogin'')
BEGIN
CREATE USER [testlogin] FOR LOGIN [testlogin];
ALTER ROLE [db_datareader] ADD MEMBER [testlogin];
END
'

EXEC(@SQL);

(Only use dynamic sql if needed)

Or without dynamic SQL if it is not needed

USE NewlyRestoredDatabase
GO
IF NOT EXISTS (
SELECT * FROM sys.sysusers where name = 'testlogin')
BEGIN
CREATE USER [testlogin] FOR LOGIN [testlogin];
ALTER ROLE [db_datareader] ADD MEMBER [testlogin];
END

Additionally, you could run a schedule a job to check if the user exists in all databases.

While doing all this, if the user already exists in the database, you have to watch out for orphaned users, and act accordingly.

If the user already exists in the restored database

Use NewlyRestoredDatabase
ALTER USER testlogin WITH LOGIN = testlogin

Question 2, now that I have the user USER_R, a user A want to connect to management studio, he doesn't see any table or stored procedure, what's missing to let them see all objects and besides be read only?

I am guessing that this user needs to be able to see all objects, and what they are? Like the T-SQL statements inside a stored procedure?

To do this, GRANT VIEW ANY DEFINITION to the login userA

USE master 
GO 
GRANT VIEW ANY DEFINITION TO userA;

The login still needs a mapped user to the databases that it needs to access, and be added to the public role (default).

Related Solutions

Sql-server – SQL Server drop and create all users in every DB

OK, there are several things that it could be but I'm not sure if we can determine which from here. These include the following:

AD propagation issues
AD caching "stickiness" on the target SQL Server
Domain Trust issues (particularly if the target server is in a different domain)
Windows Group membership/visibility issues
UAC blocking users access to certain groups needed for the new server
Kerberos issues (very common)
Etc...

And yes, dropping and re-adding the Users does seem to fix it in some of these cases, even though this should in theory never be needed because the SIDs should always match for Windows Users in transferred databases. It may be that the target SQL server simply lacks the same necessary server-level principal definitions as the original servers (and this as a side-effect creates explicit ones), or it may be that the act of adding the Login/user causes SQL Server to forcibly rerun the query and reload it's AD information for that user.

In any event, the real problem tends to be that dropping a user is a potentially disruptive and destructive act. It may work, but it may also cause some bad side-effects. Nonetheless, if you want to try that, then the best procs I know of for that are from Ted Krueger and they are located here: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users.

Other (possible) solutions that you could employ include:

Automated procedure that does not drop the User, but does add and associate a server-level Windows Login for it.
Create a Windows AD group for access to the database, add all of the users to that, and then add that to the SQL Server as a Login and Database as a User. (this is the recommended approach for managing large-scale access anyway).
...

Sql-server – Easier way to change every user in every database to default schema = dbo

So what you need to end up with is something like this, for all users in any database that don't already have a default schema of dbo:

USE db1;
ALTER USER user1 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user2 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user3 WITH DEFAULT_SCHEMA = dbo;

USE db2;
ALTER USER user4 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user5 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user6 WITH DEFAULT_SCHEMA = dbo;

Here is one way to do this, using aggregate concatenation and nested dynamic SQL, that gets something very close (the USE command is repeated for each user in any database, something you can probably resolve through more tweaking, but not sure it's necessary):

DECLARE @sql NVARCHAR(MAX) = N'', @innersql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT @innersql += ''USE ' + QUOTENAME(name) + ';
  ALTER USER '' + QUOTENAME(name) + '' WITH DEFAULT_SCHEMA = dbo;
  '' FROM ' + QUOTENAME(name) + '.sys.database_principals
  WHERE type_desc IN (N''SQL_USER'',N''WINDOWS_USER'') 
  AND default_schema_name <> ''dbo''
  AND name NOT IN (N''dbo'',N''guest'');'
FROM sys.databases WHERE state = 0;

EXEC sp_executesql @sql, N'@innersql NVARCHAR(MAX) OUTPUT', @innersql OUTPUT;

-- you can just print the command to preview it
-- (though it may be too long to review the whole thing):

PRINT @innersql;

-- once you're happy with it, uncomment this and run it all again:
-- EXEC sp_executesql @innersql;

Best Answer

Related Solutions

Sql-server – SQL Server drop and create all users in every DB

Sql-server – Easier way to change every user in every database to default schema = dbo

Related Question