Sql-server – Create limited admin role in SQL Server that cannot alter row level security

You could deny access to each individual view. The downside here is that any new views would allow alter unless you had a mechanism to deny permission such as a DDL trigger.

Alternatively, you can revoke the alter command at the schema or DB level (this may require removing a DB role) and then grant access individually to tables via some mechanism.

Another option would be to create a DDL trigger on all alter events. That trigger would check to see if the object being altered is a view via EVENTDATA and if it is make sure the logged in individual is someone with access either by a list of names or using sys.login_token to check for a domain group. If they're not then an error can be raised.

A sysadmin can't demote themselves temporarily / most of the time / etc. What you want to do is create a SQL auth login that you use most of the time, and it only has the privileges to do the limited things you want to do. Then when you want to perform DDL operations, log in as your Windows login.

There are probably ways to do this if your primary login is not a sysadmin, but I think they will be self-defeating. You'd have to log in as someone else any time you want to do anything (including things you want your lower-privileged login to be able to do, but didn't foresee when you set up the permissions). As a quick example, here are two database principals, one of which can impersonate the other:

CREATE USER blat WITHOUT LOGIN;
CREATE USER floob WITHOUT LOGIN;
GRANT IMPERSONATE ON USER::blat TO floob;

EXECUTE AS USER = 'floob';
GO
PRINT USER_NAME();
GO
EXECUTE AS USER = 'blat';
GO
PRINT USER_NAME();
GO
REVERT;
GO
PRINT USER_NAME();
GO
REVERT;

Results:

floob
blat
floob

(Now, impersonation is not perfect - there are issues with using this when you need to access resources across database boundaries, for example.)

So, imagining those are real users hooked to real logins, you could add only blat to the ddl_admin role, and log in as floob most of the time. When you needed to, you could simply impersonate blat, do your DDL things, then revert. My suggestion is to just stay away from trying to diminish the privileges of your Windows account / sysadmin, and just get used to logging in a different way so that you can be a peon most of the time, and only elevate when you need to. I'll repeat, though, that this won't eliminate mistakes you would make in any case anyway - it only prevents you from accidentally running DDL, or running the wrong DDL, which could happen when you've knowingly impersonated, too.

And, because the notion of elevating your own privileges only when you need to, in order to protect yourself because you think you'll only make mistakes when you're not elevated, seems weird to me, and because I can't help it (credit xkcd, of course):

Here is a more concrete example, that uses a real SQL authentication login.

CREATE LOGIN peon WITH PASSWORD = 'peon', CHECK_POLICY = OFF;
GO

CREATE DATABASE playground;
GO

USE playground;
GO

CREATE USER peon FROM LOGIN peon;
GO
ALTER ROLE db_datareader ADD MEMBER peon;
-- of course grant all the permissions you want here
GO

CREATE USER ddldude WITHOUT LOGIN;
GO
ALTER ROLE db_owner ADD MEMBER ddldude;
-- doesn't have to be db_owner, just a valid example
GO

GRANT IMPERSONATE ON USER::ddldude TO peon;
GO

Now, create a new session, logging in directly using peon/peon, and run this code:

USE playground;
GO
CREATE TABLE dbo.foo(id INT);
GO

Breaks, right? Yes, of course:

Msg 262, Level 14, State 1
CREATE TABLE permission denied in database 'playground'.

Now, you can impersonate another user, without being a sysadmin, so you should file a bug against whatever documentation told you that:

EXECUTE AS USER = N'ddldude';
GO
CREATE TABLE dbo.foo(id INT); -- works
GO
REVERT;

Don't forget to clean up:

USE master;
GO
ALTER DATABASE playground SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE playground;
GO
DROP LOGIN peon;
GO