SQL SERVER
Pessoal alguem sabe como dar permissão a um usuário para acesso a um banco de dados via VB(instrução SQL), pois pelo SQL enterprise menager eu não preciso..gostaria de saber a instrução SQL usada para permitir o acesso a um determinadao BD
ja tentei assim:
" EXEC sp_grantdbaccess user, login" mas só da acesso ao banco master eu gostaria de dar o acesso ao BD que eu criei......
Alguem sabe como fazer ???
ja tentei assim:
" EXEC sp_grantdbaccess user, login" mas só da acesso ao banco master eu gostaria de dar o acesso ao BD que eu criei......
Alguem sabe como fazer ???
MARCOSFSAN:
Já fiz isto a algum tempo, mas não estou achando (perdeu-se no limbo do meu HD, talvez), mas pelo que sei, vc precisa primeiro conectar com o banco em questão, para depois fazer o GRANT. Como vc verá, a SP só funciona para o "Current Data Base".
De qualquer modo, antes de eu fazer uma pesquisa, aqui vai a sintaxe da SP, retirada do SQL 2000 - Books Onle:
Espero ajudar um pouquinho.
sp_grantdbaccess
Adds a security account in the current database for a Microsoft® SQL Server™ login or Microsoft Windows NT® user or group, and enables it to be granted permissions to perform activities in the database.
Syntax
sp_grantdbaccess [@loginame =] 'login'
[,[@name_in_db =] 'name_in_db' [OUTPUT]]
Arguments
[@loginame =] 'login'
Is the name of the login for the new security account in the current database. Windows NT groups and users must be qualified with a Windows NT domain name in the form Domain\User, for example LONDON\Joeb. The login cannot already be aliased to an account in the database. login is sysname, with no default.
[@name_in_db =] 'name_in_db' [OUTPUT]
Is the name for the account in the database. name_in_db is an OUTPUT variable with a data type of sysname, and a default of NULL. If not specified, login is used. If specified as an OUTPUT variable with a value of NULL, @name_in_db is set to login. name_in_db must not already exist in the current database.
Return Code Values
0 (success) or 1 (failure)
Remarks
SQL Server usernames can contain from 1 to 128 characters, including letters, symbols, and numbers. However, usernames cannot:
Contain a backslash character (\).
Be NULL, or an empty string ('').
The security account must be granted access to the current database before it can use the database. Only accounts in the current database can be managed using sp_grantdbaccess. To remove an account from a database, use sp_revokedbaccess.
A security account for guest can be added if it does not already exist in the current database, and the login is also guest.
The sa login cannot be added to a database.
sp_grantdbaccess cannot be executed from within a user-defined transaction.
Permissions
Only members of the sysadmin fixed server role, the db_accessadmin and db_owner fixed database roles can execute sp_grantdbaccess.
Examples
This example adds an account for the Windows NT user Corporate\GeorgeW to the current database and gives it the name Georgie.
EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'Georgie'
See Also
sp_revokedbaccess
System Stored Procedures
©1988-2000 Microsoft Corporation. All Rights Reserved.
Já fiz isto a algum tempo, mas não estou achando (perdeu-se no limbo do meu HD, talvez), mas pelo que sei, vc precisa primeiro conectar com o banco em questão, para depois fazer o GRANT. Como vc verá, a SP só funciona para o "Current Data Base".
De qualquer modo, antes de eu fazer uma pesquisa, aqui vai a sintaxe da SP, retirada do SQL 2000 - Books Onle:
Espero ajudar um pouquinho.
sp_grantdbaccess
Adds a security account in the current database for a Microsoft® SQL Server™ login or Microsoft Windows NT® user or group, and enables it to be granted permissions to perform activities in the database.
Syntax
sp_grantdbaccess [@loginame =] 'login'
[,[@name_in_db =] 'name_in_db' [OUTPUT]]
Arguments
[@loginame =] 'login'
Is the name of the login for the new security account in the current database. Windows NT groups and users must be qualified with a Windows NT domain name in the form Domain\User, for example LONDON\Joeb. The login cannot already be aliased to an account in the database. login is sysname, with no default.
[@name_in_db =] 'name_in_db' [OUTPUT]
Is the name for the account in the database. name_in_db is an OUTPUT variable with a data type of sysname, and a default of NULL. If not specified, login is used. If specified as an OUTPUT variable with a value of NULL, @name_in_db is set to login. name_in_db must not already exist in the current database.
Return Code Values
0 (success) or 1 (failure)
Remarks
SQL Server usernames can contain from 1 to 128 characters, including letters, symbols, and numbers. However, usernames cannot:
Contain a backslash character (\).
Be NULL, or an empty string ('').
The security account must be granted access to the current database before it can use the database. Only accounts in the current database can be managed using sp_grantdbaccess. To remove an account from a database, use sp_revokedbaccess.
A security account for guest can be added if it does not already exist in the current database, and the login is also guest.
The sa login cannot be added to a database.
sp_grantdbaccess cannot be executed from within a user-defined transaction.
Permissions
Only members of the sysadmin fixed server role, the db_accessadmin and db_owner fixed database roles can execute sp_grantdbaccess.
Examples
This example adds an account for the Windows NT user Corporate\GeorgeW to the current database and gives it the name Georgie.
EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'Georgie'
See Also
sp_revokedbaccess
System Stored Procedures
©1988-2000 Microsoft Corporation. All Rights Reserved.
OK Marcos.
Fico satisfeito por poder ter ajudado um pouquinho.
Se o problema foi resolvido, favor encerrar o tópico.
Fico satisfeito por poder ter ajudado um pouquinho.
Se o problema foi resolvido, favor encerrar o tópico.
Tópico encerrado , respostas não são mais permitidas