Create User without ALTER ANY USER

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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