Create SQL Server grant user

Question

I created a database in SQL Server 2014 and entered with sa, is the administrator.

How do I make the sa only have permission to see procedures?

And how to create another admin user, using code?

I tried this

CREATE LOGIN ztestecom  WITH PASSWORD = 'sqlserver';  

Answer 1

You cannot remove 'sa' from being a system administrator. It is normally recommended to disable the sa account since it is a known account that will always have system administrator rights and therefore the user that is most often attempted to be hacked.

The being said, the below will create another user who will be sysadmin and then make yet another user and grant them the ability to execute stored procedures in all user databases. You can run this as your 'sa' account.

-- Create new account with a very secure password
CREATE LOGIN sqlmanager WITH PASSWORD = 'Now I am the master.';  
GO

-- Grant new account sysadmin rights
ALTER SERVER ROLE [sysadmin] ADD MEMBER [sqlmanager]
GO

-- Create new role for executing procedures only in all user db's
DECLARE @command varchar(1000)

SELECT @command = '
IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN 
    IF NOT EXISTS (select 1 from sys.database_principals where name=''db_executor'')
    BEGIN
        CREATE ROLE [db_executor];      
    END;

    GRANT EXECUTE TO db_executor;
END
'

EXEC sp_MSforeachdb @command
GO

-- Create new account for executing procedures
CREATE LOGIN sqlexecutor WITH PASSWORD = 'You run along now';
GO

-- Add new user to db_executor role in all user db db's
DECLARE @command varchar(1000)

SELECT @command = '
IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN 
    IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = ''sqlexecutor'')
    BEGIN
        CREATE USER [sqlexecutor] FOR LOGIN [sqlexecutor] WITH DEFAULT_SCHEMA=[dbo];        
    END;

    ALTER ROLE [db_executor] ADD MEMBER [sqlexecutor];
END
'

EXEC sp_MSforeachdb @command
GO

Finally log off as sa and login as sqlmanager. Ensure you have sysadmin permissions and then disable the sa account

-- Disable sa account
ALTER LOGIN [sa] DISABLE