Jag har skriptat en databas med tabeller etc.
Nu vill jag även kunna skapa SQL användare och lägga till den till databasen med fulla rättigheter till allt jag har skapat. Hur gör jag detta via SQL?
Jag tror att jag skall börja med
<code>
EXEC sp_addlogin 'anv', 'passwd', 'minDb'
</code>

Se http://doc.ddart.net/mssql/sql70/sp_adda_20.htm

<code>
sp_addlogin (T-SQL)
Creates a new Microsoft® SQL Server™ login that allows a user to connect to a server running SQL Server using SQL Server Authentication.

Syntax
sp_addlogin [@loginame =] 'login'
[,[@passwd =] 'password']
[,[@defdb =] 'database']
[,[@deflanguage =] 'language']
[,[@sid =] 'sid']
[,[@encryptopt =] 'encryption_option']

Arguments
[@loginame =] 'login'
Is the name of the login. login is sysname, with no default.
[@passwd =] 'password'
Is the login password. password is sysname, with a default of NULL. After sp_addlogin has been
executed, the password is encrypted and stored in the system tables.
[@defdb =] 'database'
Is the default database of the login (the database the login is connected to after logging in). database
is sysname, with a default of master.
[@deflanguage =] 'language'
Is the default language assigned when a user logs in to SQL Server. language is sysname, with a default
of NULL. If language is not specified, language is set to the server’s current default language (defined
by the sp_configure configuration variable default language). Changing the server’s default language does not change the default language for existing logins. language remains the same as the default language used when the login was added.
[@sid =] 'sid'
Is the security identification number (SID). sid is varbinary(16), with a default of NULL. If sid is NULL, the
system generates a SID for the new login Specifying a value other than NULL (the number must not already exist). SID is useful, for example, when you are scripting or moving SQL Server logins from one server to another and you want the logins to have the same SID between servers.
[@encryptopt =] 'encryption_option'
Specifies whether the password is encrypted when stored in the system tables. encryption_option is varchar(20), and can be one of these values.


Value Description
NULL The password is encrypted. This is the default.
skip_encryption The password is not encrypted.
skip_encryption_old The password is not encrypted. The supplied password was encrypted by an earlier version of SQL Server. This option is provided for upgrade purposes only.


Return Code Values
0 (success) or 1 (failure)

Remarks
SQL Server logins and passwords can contain from 1 to 128 characters, including letters, symbols, and
numbers. However, logins cannot:

Contain a backslash character (\).
Be a reserved login name, for example sa or public, or already exist.
Be NULL or an empty string (‘‘).
If the name of a default database is supplied, you can connect to the specified database without
executing the USE statement. However, you cannot use the default database until given access to that
database by the database owner (using sp_adduser or sp_addrolemember) or sp_addrole.

The SID number is the unique Microsoft Windows NT® user identification number. The SID is guaranteed
to be unique (it is a GUID) for each user in a Windows NT domain. SQL Server automatically uses the
Windows NT SID to identify Windows NT users and groups, and generates a SID for SQL Server logins.

Using skip_encryption to suppress password encryption is useful if the password is already in encrypted form when the login is added to SQL Server. If the password was encrypted by an earlier version of SQL
Server, use skip_encryption_old.

sp_addlogin cannot be executed from within a user-defined transaction.

The table shows several stored procedures that can be used in conjunction with sp_addlogin.

Stored procedure Description
sp_grantlogin Adds a Windows NT user or group.
sp_password Changes a user’s password.
sp_defaultdb Changes a user’s default database.
sp_defaultlanguage Changes a user’s default language.


Permissions
Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlogin.

Examples
A. Create a login ID with no password and master default database
This example creates a SQL Server login for the user Victoria, without specifying a password or default database.

EXEC sp_addlogin 'Victoria'



B. Create a login ID and default database
This example creates a SQL Server login for the user Albert, with a password of food and a default
database of corporate.

EXEC sp_addlogin 'Albert', 'food', 'corporate'



C. Create a login ID with a different default language
This example creates a SQL Server login for the user Claire Picard, with a password of caniche, a default
database of public_db, and a default language of French.

EXEC sp_addlogin 'Claire Picard', 'caniche', 'public_db', 'french'



D. Create a login ID with a specific SID
This example creates a SQL Server login for the user Michael, with a password of chocolate, a default database of pubs, a default language of us_english, and a SID of 0x12345678.

EXEC sp_addlogin 'Michael', 'chocolate', 'pubs', 'us_english', '0x12345678'



E. Create a login ID and do not encrypt the password
This example creates a SQL Server login for the user Margaret with a password of Rose on Server1, extracts the encrypted password, and then adds the login for the user Margaret to Server2 using the
previously encrypted password, but does not further encrypt the password. User Margaret can then log in to Server2 using the password Rose.

-- Server1

EXEC sp_addlogin Margaret, Rose



--Results

New login created.



-- Extract encrypted password for Margaret

SELECT CONVERT(VARBINARY(32), password)

FROM syslogins

WHERE name = 'Margaret'



--Results

------------------------------------------------------------------

0x2131214A212B57304F5A552A3D513453



(1 row(s) affected)



-- Server2

EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@encryptopt = 'skip_encryption'


</code>

Jo så långt hade jag med kommigt. Men med sp_addLogin lägger jag ju bara till en användare och berättar vilken som är default databas. Jag ger ju inte användaren några rättigheter eller en access till databasen eller tabeller och stored procedures.

(För övrigt så är det väl brott mot upphovsrätten att kopiera in en massa text från någon annan webbsida även om man hänvisar till den.)

Den sidan har ju ändå inte gjort annat äm kopierat texten i Books Online för att göra den tillgänglig på internet (tidigare fanns inte Books Online online ;)

Dessutom är den för SQL Server 7. Ett enkelt tips, använd Books Online. Microsoft är i allmänhet bra på dokumentation, och för SQL Server skulle jag säga att de är speciellt bra på det. Start, All Programs, Microsoft SQL Server, Books Online. Det du söker är sp_grantdbaccess. Se även min artikelserie om just detta här på Pellesoft: Artikel [Användare, roller och rättigheter i SQL Server - del 1]

Jag har kommit lite längre på vad jag vill göra nu vill jag bara itterrera över alla mina egna stored procedures med!
<code>
EXEC sp_addlogin 'theUser', 'passwd', 'theDB'
go
use theDB
go
sp_grantdbaccess 'theUser'

go
GRANT ALL ON myStoredP1 TO theUser
GRANT ALL ON myStoredP2 TO theUser
GRANT ALL ON myStoredP3 TO theUser
GRANT ALL ON myStoredP4 TO theUser

/* Skulle helre vilja ha något som ittererar över alla stored procedures som jag själv skapat */
DECLARE @@sp_name varchar(40)
DECLARE sp_cursor CURSOR FOR
select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1

OPEN sp_cursor

FETCH NEXT FROM sp_cursor INTO @@sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
/* men @@sp_name fungerar inte på nästa rad :( */
GRANT ALL ON @@sp_name TO test1
/* print @@sp_name */
FETCH NEXT FROM sp_cursor
END

CLOSE sp_cursor
DEALLOCATE sp_cursor
GO

</code>


(Det som var mest intressant var att Pelle kopierade in texten. Om någon skall förgå med gott exempel så borde det ju vara han. Och att säga att det är ok för att någon annan gör det har ju inget med upphovsrätten att göra.)

Om du nödvändigtvis vill utföra detta med en cursor (= fult och dumt - bara min åsikt...)
Så kan du göra såhär:

EXEC ('GRANT ALL ON ' + @sp_name + ' TO test1' )

Eventuellt kan du behöva göra strängvariabeln komplett innan.

Ha bara ett snabel-a i variabelnamnet, annars simulerar du globala parametrar - och det vill du inte bli förväxlad med.

/micke

Lösningen!

(Jag undrrar lite varför det är fult och dumt med en cursor, är väl i alla fall snyggare än att behöva skriva en GRANT för varje storedProcedure)

<code>CREATE PROCEDURE addAndGrantUser
@theUser nvarchar(40),
@thePasswd nvarchar(40),
@theDB nvarchar(40)
AS
EXEC sp_addlogin @theUser, @thePasswd, @theDB


EXEC ('sp_grantdbaccess ' + @theUser)

DECLARE @sp_name varchar(40)
DECLARE sp_cursor CURSOR FOR
select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1

OPEN sp_cursor

FETCH NEXT FROM sp_cursor INTO @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ( 'GRANT ALL ON ' + @sp_name + ' TO ' + @theUser)
FETCH NEXT FROM sp_cursor INTO @sp_name
END

CLOSE sp_cursor
DEALLOCATE sp_cursor

go
addAndGrantUser 'myUser','myPasswd','myDb'

</code>