To add a user to a role, you must use the stored procedure sp_addrolemember. First you must select the database that contains the user and the role. [cc lang=”sql”] USE YourDatabaseHere GO EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_accessadmin’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_backupoperator’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_ddladmin’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_denydatareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_denydatawriter’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_owner’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_securityadmin’, ‘JohnJacobs’ [/cc] The example above shows all predefined roles in a database.
Continue reading ...
There are two methods to add a user in SQL Server. One way is to do so using code (programmatically), the second way is to use the interface. First we will explore the programmatic way, then we will walk through the interface. First of all, there are two different ways users can login to SQL Server. One is automatically using their windows accounts or (Windows Authentication), the other is by using SQL Server Authentication. When a user is created in SQL using SQL Authentication, the user will have to type in the username and password manually in order to connect. Windows Authentication – The user will connect to SQL Server automatically using their existing credentials without having to type in their username or password. (More Secure) SQL Authentication – The user will be prompted to type in the username and password manually in order to connect. (Less Secure) Add User Using Windows Authentication [cc lang=”sql”] — Create user windows Authentication CREATE LOGIN [YourDomainNameJohnJacobs] FROM WINDOWS WITH DEFAULT_DATABASE = [YourDatabaseHere]; GO — Now add user to database USE YourDatabaseHere; CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs]; — If adding to a second database, do so below: USE YourSecondDatabaseHere; CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs]; [/cc] Add User Using SQL Authentication [cc lang=”sql”] — Create user for SQL Authentication CREATE LOGIN JohnJacobs WITH PASSWORD = ‘JinGleHeimerSchmidt’ ,DEFAULT_DATABASE = [YourDatabaseHere] GO — Now add user to database USE YourDatabaseHere; CREATE USER JohnJacobs FOR LOGIN JohnJacobs; GO — If adding to a second database, do […]
Continue reading ...
If you try to drop a user that owns a schema, you will receive the following error message: [code] The database principal owns a schema in the database, and cannot be dropped. [/code] In order to drop the user, you need to find the schemas they are assigned, then transfer the ownership to another user or role [cc lang=”sql”] SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID(‘joe’) — now use the names you find from the above query below in place of the SchemaName below ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo [/cc]
Continue reading ...
