May 6, 2016 at 10:11 am
Hi,
I need to give the ability to create user to 'myuser' but 'myuser' must not be able to add or remove users in sysadmin role. Is there any way to do that?
Thanks
May 6, 2016 at 10:54 am
remember there is a difference between a LOGIN and a USER.
nothing gets any rights unless you add them to a group or role that gives them permissions.
so, never add a LOGIN as sysadmin, and they can never add anyone else as sysadmin either.
in a specific database, anyone in the db_owner role can create a USER for that specific database, but not do anything to their logins (assuming you did not give them sysadmin)
scripting wise, here's an example or two.
--create our normal user
CREATE LOGIN [ClarkKent] WITH PASSWORD=N'NotTh3ARealPassword',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;
GO
USE [SandBox]
GO
CREATE USER ClarkKent For Login ClarkKent;
GO
ALTER USER ClarkKent with DEFAULT_SCHEMA = 'dbo'
EXECUTE AS User='ClarkKent'
select * from sys.objects --cannot see anything, nothing granted to him so far
REVERT; --change back to the superuser
EXEC sp_addrolemember 'db_datareader','ClarkKent ';
EXECUTE AS User='ClarkKent'
select * from sys.objects --can NOW see all the tables and views and query them
REVERT; --change back to the superuser
Lowell
May 6, 2016 at 11:24 am
I'm sorry, you're right.
I have an app that must create login and user, but not be able to change their server roles. Is it possible?
In my search I found ADD_SERVER_ROLE_MEMBER to trigger and rollback when anyone put a login in sysadmin server role.
Is that the best option?
Thanks for your help.
May 6, 2016 at 11:42 am
so your applicaiton has sysadmin rights, and whenever it creates a login, it makes it sysadmin, and then adds a user i guess.
if you kill it in a trigger, you'll break the application, so i would go to the server and create a specific list of the real sysadmins that should be allowed.
then i'd just create a job that runs multiple times per day that removes rights from anyone not on that list
something like this gets you started on the idea: i guess i would generate the commands via FOR XML and execute one big string;
it'll require a lot of review to get the right users seperated
select is_SRVROLEMEMBER(name),* from master.sys.server_principals WHERE is_SRVROLEMEMBER('sysadmin',name) = 1 AND type_desc IN('SQL_LOGIN','windows+login')
and name not in
('sa',
'NT AUTHORITY\SYSTEM',
'NT SERVICE\MSSQLSERVER',
'MyDomain\Domain Admins',
'NT SERVICE\SQLSERVERAGENT',
'MyDomain\lowell')
Lowell
May 6, 2016 at 12:44 pm
Ok, thank you. You guide me to find out my mistake.
ALTER ANY LOGIN does not give to user the right to add it to sysadmin or any other server role and allows to create, modify and drop login.
There are too many wrong answers inducing to interpretation errors to ALTER ANY LOGIN, like this one.
Thank you, have a nice weekend.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy