| Author |
SQL2000 UserPermissions for Database
|
|
| Arejan 2006-12-03, 7:15 pm |
| there are 5 databses in sql server.
created a user Userx
and set the dataccess for databaseA and databaseF as follows
public
db_backupoperator
db_datread
db_datawrite
(the user can only edit del add and backup records/tables)
1.whats the command to select only databases whose user is "Userx"
2.how to restore the windows group builtin\administrato
r once deleted
VB6 Qstion
3. Sqldmo and SQLDMO.RegisteredServer needs client tools to be
installed in a PC in lan ?
are there any dlls that can be included to run SqlDmo?
| |
| Uri Dimant 2006-12-04, 5:16 am |
| Arejan
> 1.whats the command to select only databases whose user is "Userx"
EXEC sp_grantdbaccess 'Login', 'Userx'
> 2.how to restore the windows group builtin\administrato
r once deleted
Type BUILTIN/Administrators in the NAME text box
> 3. Sqldmo and SQLDMO.RegisteredServer needs client tools to be
> installed in a PC in lan ?
> are there any dlls that can be included to run SqlDmo?
>
It is library to work against SQL Server from the client side. Yes
"Arejan" <areejan2000@yahoo.com> wrote in message
news:1165178556.581889.74120@16g2000cwy.googlegroups.com...
> there are 5 databses in sql server.
>
> created a user Userx
>
> and set the dataccess for databaseA and databaseF as follows
>
> public
> db_backupoperator
> db_datread
> db_datawrite
>
> (the user can only edit del add and backup records/tables)
>
>
>
> 1.whats the command to select only databases whose user is "Userx"
>
> 2.how to restore the windows group builtin\administrato
r once deleted
>
>
> VB6 Qstion
>
> 3. Sqldmo and SQLDMO.RegisteredServer needs client tools to be
> installed in a PC in lan ?
> are there any dlls that can be included to run SqlDmo?
>
| |
| Arejan 2006-12-04, 5:16 am |
| Thanks Uri
>EXEC sp_grantdbaccess 'Login', 'Userx'
its EXEC sp_grantdbaccess 'Userx'
will it cover
public
db_backupoperator
db_datread
db_datawrite
or only db_datread
> 1.whats the command to select only databases whose user is "Userx"
> set the dataccess for databaseA and databaseF
here "Userx" can r-w-backup databaseA and databaseF
i want to
select all databases that "Userx" can access.
which will display
databaseA
databaseF
Thank You
| |
| Uri Dimant 2006-12-04, 5:16 am |
| Arejan
By EXEC sp_grantdbaccess 'Login', 'Userx' , you are only givinng an simple
access to the database
Now that , you add the user to the database fixed role such you mentioned,
just do not make a login to be a member of sysadmin server role
"Arejan" <areejan2000@yahoo.com> wrote in message
news:1165216099.346721.194610@n67g2000cwd.googlegroups.com...
> Thanks Uri
>
>
> its EXEC sp_grantdbaccess 'Userx'
>
> will it cover
> public
> db_backupoperator
> db_datread
> db_datawrite
>
> or only db_datread
>
>
>
>
>
>
>
> here "Userx" can r-w-backup databaseA and databaseF
>
> i want to
> select all databases that "Userx" can access.
>
> which will display
>
> databaseA
> databaseF
>
> Thank You
>
| |
| Arejan 2006-12-04, 7:13 pm |
| Ok
how to list all the databases that Userx can login
| |
| Dan Guzman 2006-12-05, 7:13 pm |
| > how to list all the databases that Userx can login
Try:
EXEC sp_helplogins 'Userx'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Arejan" <areejan2000@yahoo.com> wrote in message
news:1165249163.905889.70140@79g2000cws.googlegroups.com...
> Ok
>
> how to list all the databases that Userx can login
>
| |
| Arejan 2006-12-05, 7:13 pm |
|
Dan
i get
Userx databaseA db_backupoperator MemberOf
Userx databaseA db_datareader MemberOf
Userx databaseA db_datawriter MemberOf
Userx databaseA Userx MemberOf
Userx databaseF db_backupoperator MemberOf
Userx databaseF db_datareader MemberOf
Userx databaseF db_datawriter MemberOf
Userx databaseF Userx MemberOf
I need to populate a combobox for login for userx which will get this
databaseA
databaseF
select database from sysobjects where username ='userx' ?
| |
| Dan Guzman 2006-12-06, 12:13 am |
| > select database from sysobjects where username ='userx' ?
Try:
SELECT name
FROM master.dbo.sysdatabases
WHERE HAS_DBACCESS(name) = 1
ORDER BY name
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Arejan" <areejan2000@yahoo.com> wrote in message
news:1165334406.579473.299020@73g2000cwn.googlegroups.com...
>
> Dan
> i get
>
> Userx databaseA db_backupoperator MemberOf
> Userx databaseA db_datareader MemberOf
> Userx databaseA db_datawriter MemberOf
> Userx databaseA Userx MemberOf
> Userx databaseF db_backupoperator MemberOf
> Userx databaseF db_datareader MemberOf
> Userx databaseF db_datawriter MemberOf
> Userx databaseF Userx MemberOf
>
>
> I need to populate a combobox for login for userx which will get this
>
> databaseA
> databaseF
>
> select database from sysobjects where username ='userx' ?
>
| |
| Arejan 2006-12-07, 5:18 am |
| Dan
> SELECT name
> FROM master.dbo.sysdatabases
> WHERE HAS_DBACCESS(name) = 1
> ORDER BY name
gives a list of all databases
databaseA
databaseB
databaseC
databaseD
databaseE
databaseF
master
model
pubs
....
( log in as sa (administrator))
i want to show only 2 databases of which Userx has previlage to login
Database user
databaseA Userx
databaseF Userx
as other databses are not used in this context
is there an select query for this?
| |
| Dan Guzman 2006-12-07, 7:12 pm |
| Since your last message mentioned loading a combobox, I assumed you wanted a
list of databases the current user could access rather than an admin
application, presumably run by privileged users. I believe the proc below
will meet your requirement and you can execute the code directly if needed.
CREATE PROC dbo. usp_ListDatabasesFor
Login
@Login sysname = NULL
AS
SET NOCOUNT ON
DECLARE
@DatabaseName sysname,
@SqlStatement nvarchar(4000)
--use current login if @Login not specified
IF @Login IS NULL SET @Login = SUSER_SNAME()
IF OBJECT_ID(N'tempdb..#DatabaseList') IS NOT NULL
DROP TABLE #DatabaseList
CREATE TABLE #DatabaseList
(
DatabaseName sysname
)
DECLARE DatabaseList
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT name
FROM master..sysdatabases
WHERE HAS_DBACCESS(name) = 1
OPEN DatabaseList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DatabaseList INTO @DatabaseName
IF @@FETCH_STATUS = -1 BREAK
SET @SqlStatement =
N'USE ' + @DatabaseName +
N' IF EXISTS(
SELECT 1
FROM dbo.sysusers
WHERE
sid = SUSER_SID(@Login) OR
IS_SRVROLEMEMBER(''s
ysadmin'', @Login) = 1 OR
(name = N''guest'' AND hasdbaccess = 1)
)
BEGIN
INSERT INTO #DatabaseList
VALUES(@DatabaseName
)
END'
EXEC sp_executesql
@SqlStatement,
N'@Login sysname, @DatabaseName sysname',
@Login = @Login,
@DatabaseName = @DatabaseName
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
SELECT DatabaseName
FROM #DatabaseList
ORDER BY DatabaseName
DROP TABLE #DatabaseList
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Arejan" <areejan2000@yahoo.com> wrote in message
news:1165483192.541450.79840@16g2000cwy.googlegroups.com...
> Dan
>
>
> gives a list of all databases
>
> databaseA
> databaseB
> databaseC
> databaseD
> databaseE
> databaseF
> master
> model
> pubs
> ...
>
> ( log in as sa (administrator))
>
> i want to show only 2 databases of which Userx has previlage to login
>
>
> Database user
> databaseA Userx
> databaseF Userx
>
> as other databses are not used in this context
>
> is there an select query for this?
>
| |
| Arejan 2006-12-08, 7:13 pm |
| logged in as userx
exec usp_ListDatabasesFor
Login return
EXECUTE permission denied on object 'usp_ListDatabasesFo
rLogin',
database 'databaseA', owner 'dbo'.
but sa(admin) can
meanwhile i have created Newsp_helplogins under master and this will
update the
new_userdb_Table with dbname and user (#tb2_PlainLogins is removed)
but then userx cannot exec this too.
how do i set permission to run and "not to create" a sp for a user ?
or call a trigger that will run the sp as sa(admin) or if possible
any user and retrive data from the table.
| |
| Dan Guzman 2006-12-09, 12:13 am |
| > logged in as userx
>
> exec usp_ListDatabasesFor
Login return
>
> EXECUTE permission denied on object 'usp_ListDatabasesFo
rLogin',
> database 'databaseA', owner 'dbo'.
As with any stored procedure, the invoking user needs execute permissions,
either directly or via role membership. Try:
GRANT EXEC ON dbo. usp_ListDatabasesFor
Login TO userx
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Arejan" <areejan2000@yahoo.com> wrote in message
news:1165609013.358290.126580@16g2000cwy.googlegroups.com...
> logged in as userx
>
> exec usp_ListDatabasesFor
Login return
>
> EXECUTE permission denied on object 'usp_ListDatabasesFo
rLogin',
> database 'databaseA', owner 'dbo'.
>
> but sa(admin) can
>
>
> meanwhile i have created Newsp_helplogins under master and this will
> update the
>
> new_userdb_Table with dbname and user (#tb2_PlainLogins is removed)
>
>
> but then userx cannot exec this too.
>
>
> how do i set permission to run and "not to create" a sp for a user ?
>
> or call a trigger that will run the sp as sa(admin) or if possible
> any user and retrive data from the table.
>
| |
| Arejan 2006-12-09, 5:13 am |
| returns
There is no such user or group 'userx'.
?
| |
| Arejan 2006-12-09, 5:13 am |
| Server: Msg 4604, Level 16, State 1, Line 7
There is no such user or group 'userx'
| |
| Arejan 2006-12-09, 5:13 am |
| Thank you Dan for keeping up with me.
:)
logged in as sa and executed
GRANT EXEC ON dbo. usp_ListDatabasesFor
Login TO userx
again loged in as userx and run EXEC dbo. usp_ListDatabasesFor
Login
returns error
User does not have permission to perform this action.
next i tested on creating a role
test_exec_usp_ListDa
tabasesForLoginROLE
and add userx to the role
but login-userx returns
User does not have permission to perform this action.
where have i gone wrong ?
whats the steps to let a user run a stored procedure?
| |
|
|
| Arejan 2006-12-10, 12:12 am |
| no body?
| |
| Dan Guzman 2006-12-11, 7:14 pm |
| Sorry I didn't response earlier Arejan. I thought you had solved your
problem by creating a role.
> User does not have permission to perform this action.
I cannot reproduce this error using the proc I posted earlier. Have you
changed it to include other functionality? I ran the following:
1) created stored procedure
2) created role and assigned proc permissions:
EXEC sp_addrole 'MyRole'
GRANT EXECUTE ON dbo. usp_ListDatabasesFor
Login TO MyRole
3) created, login, user and added user to role:
EXEC sp_addlogin 'Userx', 'UserxPassword'
EXEC sp_adduser 'Userx'
EXEC sp_addrolemember 'MyRole', 'Userx'
4) logged in as Usex and successfully executed proc
EXEC usp_ListDatabasesFor
Login 'SomeLogin'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Arejan" <areejan2000@yahoo.com> wrote in message
news:1165653122.615048.4730@j72g2000cwa.googlegroups.com...
> Thank you Dan for keeping up with me.
>
> :)
>
> logged in as sa and executed
>
> GRANT EXEC ON dbo. usp_ListDatabasesFor
Login TO userx
>
> again loged in as userx and run EXEC dbo. usp_ListDatabasesFor
Login
>
> returns error
>
> User does not have permission to perform this action.
>
>
>
>
> next i tested on creating a role
> test_exec_usp_ListDa
tabasesForLoginROLE
>
> and add userx to the role
>
>
> but login-userx returns
>
> User does not have permission to perform this action.
>
> where have i gone wrong ?
>
> whats the steps to let a user run a stored procedure?
>
|
|
|
|