Sql-server – Will SYSTEM_USER return the unique user id for users authenticated by Active Directory

sql serversql-server-2008

I want to set up Active Directory authentication for my users, who will be assigned to specific database roles. I also need to log activity and ensure I have captured the IDs of the indivudals connected.

I have found simple instructions on how to do it. ( How do I assign an entire Active Directory group security access in SQL Server 2008? )

… But Will SYSTEM_USER return the logon id of the active directory connection or the individual user? (Unfortunately I cannot test this!)

Best Answer

A call to system_user will return the AD user's name in your case. For instance, if you have a user set up as YourDomain\YourUser1:

select system_user

-- this returns "YourDomain\YourUser1"

If YourDomain\YourUser1 is part of a AD group (i.e. YourDomain\YourGroup1), then it would still have the same output as above (returning "YourDomain\YourUser1").

Related Solutions

SQL Server 2008 – How to Assign Active Directory Group Security Access

Set the AD group as a login. And "login" means server level login not the AD concept of user/login. In SQL Server speak, this is a server level principal
Create a mapped user in. You shouldn't really permission a user directly on tables. And "user" means database user not the AD concept of user: in SQL Server speak, this is a "database level principal"
Add user to role (also a "database level principal")
GRANT permissions to the roles on the tables (a table or proc etc is a "securable")

Sample script

USE master;
GO
CREATE LOGIN [MYDOMAIN\APPLICATION SUPPORT] FROM WINDOWS;
GO
USE mydb;
GO
CREATE USER [MYDOMAIN\APPLICATION SUPPORT] FROM LOGIN [MYDOMAIN\APPLICATION SUPPORT];
GO
CREATE ROLE rSupport;
GO
EXEC sp_addrolemember 'rSupport', 'MYDOMAIN\APPLICATION SUPPORT';
GO
GRANT SELECT, INSERT,UPDATE, etc ON Mytable TO rSupport;
GO

sp_addrolemember is deprecated starting with SQL Server 2012, where ALTER ROLE should be used instead.

Sql-server – can I create an SSRS group without adding the group to the box itself and without using Active Directory

There are a couple of courses of action for you:

If you're friendly with your Windows Admin, I'd thoughtfully think out some AD Groups and then present them to the admin and ask them to be created (in whatever workflow you have).
From there you can assign permissions on the individual folders by these groups. Click on the folder, then click on Properties.

enter image description here

On the left you will see "Security," click that. Then click "Edit Item Security." You may get a dialog that tells you that the item currently inherits from parent. You may need to wait for a bit before this completes, but be patient.

enter image description here

If successful you will have checkboxes next to the principles already with access. From here choose "New Role Assignment"

enter image description here

Now you can add your AD Group in the next page and assign it the permissions necessary. Try with DOMAIN\GroupName

enter image description here

Obviously this will be much easier if you have AD groups, but if you can't get that, then you can do this with individual domain users. That would be a management nightmare?

Best Answer

Related Solutions

SQL Server 2008 – How to Assign Active Directory Group Security Access

Sql-server – can I create an SSRS group without adding the group to the box itself and without using Active Directory

Related Question