Add an account for application use in SQL Server

In previous customer consultation cases, many client applications connected SQL Server directly with the SA account. If you are a little more strict with database management, you should not give the application this permission, usually the application only needs to make a pruning check, and very few DDL operations, so configure the account should follow the principle of "minimum permission allocation" only give the required permissions.

For an application, the least privilege is usually to give Read permissions, write permissions, and execute stored procedure permissions. Because to prevent the disclosure of database information caused by SQL injection, you also need to consider the View definition permission for the denied account, but it is worth noting that BULK INSERT fails if you deny permission to view the definition. The complete permissions are defined as follows:

alter role [db_datareader] add MEMBER user name alter role [db_datawriter] Add MEMBER user name grant execute to user name deny view definition to User name

In SQL Server, the instance level is the login name, and the database level is the user name, and the login can be mapped to a specific library after it is created. So I wrote a complete script that simultaneously created the login name, the user, and given the corresponding permissions, the script is as follows:

--Create a user's stored procedure,--Example EXEC sp_createuser ' UserName ', ' rw ', ' DatabaseName '--exec sp_createuser ' Tesefx ', ' r ', ' Test ', ' 0xe39ca97ebe03bb4ca5ff78e50374eebb ' CREATE PROC sp_createuser @loginName varchar, @IsWrite varchar (3), @ DatabaseName varchar, @Sid varchar = ' 1 ' as PRINT (' Example: EXEC sp_createuser ' UserName ', ' rw ', ' DatabaseName ' PRINT (' Example: EXEC sp_createuser ' ' UserName ', ' rwv ', ' DatabaseName ', ' 0xe39ca97ebe03bb4ca5ff78e50374eebb ') PRINT (' r is read-only, RW is read-write, RWV is read-write plus view definition permission ') IF EXISTS (SELECT name from sys.syslogins WHERE name = @loginName) Begi n Print n ' login already exists, skip create login step ' END ELSE BEGIN DECLARE @CreateLogin NVARCHAR (DECLARE) @pwd VARCHAR PRINT @Sid SET @pw D=newid () IF (@sid = ' 1 ') BEGIN SET @CreateLogin = ' CREATE LOGIN [' + @loginName + '] with Password=n ' + @Pwd + ', defaul T_database=[master], Check_expiration=off, Check_policy=off; ' PRINT N ' login is created with password: ' [email protected] END ELSE BEGIN SET @CreateLogin = ' Create login [' + @loginName + '] with PASSW ORD=n ' + @Pwd + ' ', Default_database=[master], Check_expiration=off, check_policy=off,sid= ' [email protected]+ '; ' PRINT N ' has created login with Sid: ' [email protected] END EXEC (@CreateLogin)--declare @sidtemp NVARCHAR--select @sidtemp = Sid from Sys.server_principals WHERE [email protected]--print (N ' Login name: ' [email protected]+n ' sid: 0x ' + CONVERT (VARCHAR (), @sidtemp, 2)) END DECLARE @DynamicSQL NVARCHAR (1000)--Toggle Database context SET @DynamicSQL = N ' use [' + @Databa SeName + ']; ' + ' IF EXISTS (SELECT name from sys.database_principals WHERE name= ' [email protected]+ ') Begin Print (' username already exists, Skip create User name step ') End else begin create user [' + @loginName + '] for LOGIN ' + @loginName + ' end;if (' + ' + @IsWrite + ' = ') RW ' or ' + @IsWrite + ' = ' RWV ') BEGIN ALTER ROLE [db_datareader] ADD MEMBER ' + @loginName + '; ALTER ROLE [db_datawriter] ADD MEMBER ' + @loginName + '; END ELSE BEGIN ALTER ROLE [db_datareader] ADD MEMBER ' + @loginName + '; ALTER ROLE db_datawriter DROP MEMBER ' + @lOginname + '; End;grant execute to ' + @loginName + '; if (' [email protected]+ ' <> ' RWV ') Begin deny view definition to ' + @loginName + '; End ELSE begin grant View definition to ' + @loginName + ';  End ' EXEC (@DynamicSQL)

The stored procedure is used to create the logins, users, and corresponding permissions required for the application to connect to SQL Server, and the steps are skipped when the user or login is present, such as the following:

The first line of execution above is to create a standard account name username, give read and write permission to the Databasenam library, and return the generated GUID password. The second stored procedure uses the fourth parameter SID to create a login, since both logins require the same SID in an AlwaysOn or mirrored environment, providing a way to create a login using the SID in that case.

If necessary, you can modify the stored procedure as you want.

Add an account for application use in SQL Server